|
I am trying to read a CSV file using Openrowset import option. In the CSV file I am having text and numeric data. numeric data is having thousand seperator and is in quotes (e.g. "1,123,124.510"). I am not able to read this data as it getting split on comma and openrowset read it as 3 columns "1 | 123 | 124.510". Can any one help how to solve this problem. Thanks in advanceI am using the format file as the data file is big and I am reading the selected columns. In the file only numeric data is comming in quotes "" + thousand seperator Also I am getting this file from external source. I can update it after I receive it but for that I have to write a different process. I have checked the Import & Export data wizard of SQL Server it also split the numeric data but when we set Text Qualifier = " it works..... Is there a way by which we can set Text Qualifier in format file or in Openrowset OR there no way on SQL Server to read this kind of files via T-SQL command.
(comments are locked)
|
|
In the schema.ini you are using, did you specify all the columns and their types? Using quotes as the text qualifier should work perfectly if everything else is set properly. There is one general article that I wrote some time ago that may help you with this: http://www.sqlservercentral.com/articles/OpenDataSource/61552/ And the official microsoft reference for the schema.ini is at http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx
(comments are locked)
|
|
Try this... SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\Temp;HDR=NO;FMT=Delimited', 'SELECT * FROM ImportTest.csv') Of course, you need to change the "Database" and "From" clauses to match your file. The FMT=Delimited is what enables the string qualifier. The best thing to do, however, is make whoever is giving you these files to format them correctly.
(comments are locked)
|
