August 26, 2011 at 10:44 am
So here is the deal i need to create business sales reports from data saved in a csv on a daily basis. I am thinking i will import that data into SQL, but not sure what is the best way to handle it.
what do you guys think? .net app that creates a spreadsheet from that data? or is there another way i don't know about?
This is going to be my first project dealing with report type forms so i figured i would ask.
Thanks guys
August 26, 2011 at 11:22 am
So you need to create a report in excel that uses excel as its datasource? Unless I am missing something I don't know why you would even bother with sql. Can you just create a report in Excel based on the data in Excel?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2011 at 11:52 am
SSIS is a great way to automate imports of datafiles... if you need to import and manipulate .csv data into SQL Server SSIS is the way to do it.
August 26, 2011 at 2:27 pm
I'd agree with NJ-DBA...use SSIS and schedule your dtsx as a schedule SQL Agent job that you can then trigger your reports from. However, as a simple test you can try the code below.
You can google or check BOL to get all the fancy options that come along with the BULK INSERT task but this will give you the general idea (pretty simple really)
USE sandbox
GO
CREATE TABLE CSVTest (
ID int,
FirstName VARCHAR(5),
LastName VARCHAR(5),
BirthDate SMALLDATETIME
)
/* Create, then save a CSV or TXT file with some test data
1, John, Doe, 19751122
2, Jane, Doe, 19790617
*/
BULK INSERT CSVTest
FROM 'c:\csvtest.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
/* Test */
SELECT * FROM CSVTest
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply