Importing TRUE CSV data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Importing TRUE CSV data

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-07-14 : 17:06:30

Need expert suggestion for handling one issue…

We have flat files coming in that will be comma separated with quotes only on those fields that has comma embedded (true csv). How can we handle this? We need to stage these files and do further processing. I was about to create a table using the first record and then create format file to load the data, but this Quotes for few fields that has comma messes up my approach. Please let me know anything that I can use programatically to take care of this situation. Someone suggested me LogParser...anyone have experience with this? Thanks a lot for your help.

KeyCode, MemAcctNum, NATitle, Name
205509,0505488528,,"Rip, Winkle"
205501,0505488111,,"Tiger, Scott"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-07-14 : 20:48:38

I normally open the file in excel that way I can set the delimiters and quotes accordingly. From there I just save it in a xls format because I prefer working with that format due to as long as you verify the columns, there is no chance of a extra comma or quote throwing off your data.

From that point you can use a openrowset


--MAKE SURE TO CHANGE TO YOUR FILE PATH, AND ENTER 
--THE CORRECT SHEET NAME.
select * 
into #TESTXLS 
from openrowset('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\TEST.xls;HDR=YES;IMEX=1', 
'select * from [Sheet1$]')

Also, FYI: don't say you need a expert sugestion... Just post your question, and if someone feels they can contribute they will. By saying you need a "experts sugestion" you will lessen your chance of someone providing you with the answer you were looking for.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-07-14 : 21:01:49

Hey Vinnie thanks for the response... sorry for putting that expert suggestions... I did not think anything, but needed suggestion from people who knew this.. My problem is that the data will come in flat file and can have more than million records also the files can have different columns. Thanks for your time.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-07-14 : 21:13:50

The one million records is a issue. Query the flat file directly.

I used the test information you provide and the below query returend correctly.


--MAKE SURE TO CHANGE C:\MYCSV to the directory for the flat files
--, then change the test.csv to your flat file name.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

SELECT * FROM 
OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\MYCSV;', 'SELECT * from test.csv');

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-07-14 : 21:46:32

Issue for this is that there is no 64 bit for The OLE DB provider "MSDASQL"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-07-14 : 22:23:12

You can get the 64 bit driver here.

http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

GhantaBro
Posting Yak Master

215 Posts