Importing CSV files with optional fields

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Importing CSV files with optional fields

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-21 : 08:29:55

Samuel writes "I'll try to state my question clearly, but I've been going around in circles for the the last 2 days and am not thinking that clearly right now...

First, I'll describe what I am trying to do, and then let you know what I have tried and the results..

I need to import data from CSV files. The files currently have six columns, but three more columns are going to be added (maybe one at a time, maybe all at once) so I want to check for the existance of the optional columns and add default data if they don't. The procedure is being called from a web application.

I initially tried Bulk Insert, but couldn't get the optional fields working correctly (even with a .fmt file).

Next I tried OpenRowSet, and created a Schema.ini file that defined all possible columns,
SELECT isnull(a.optCol1, 'default1'),
isnull(a.optCol2,2),
isnull(a.optCol3,'default3'),
cast(a.reqCol1 as varchar(50)),
cast(a.reqCol2 as varchar(30)),
cast(a.reqCol3 as varchar(50)),
cast(a.reqCol4 as int),
cast(a.reqCol5 as varchar(50)),
cast(a.reqCol6 as varchar(50))
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=d:\datadir\;Extensions=CSV;',
'SELECT * FROM DataFile.csv') a

scheme.ini
[DataFile.csv]
ColNameHeader=true
Format=CSVDelimited
MaxScanRows=0
CharacterSet=OEM
Col1=optCol1 Text Width 50
Col2=optcol2 Short
Col3=optCol3 Text Width 50
Col4=reqCol1 Text Width 30
Col5=reqCol2 Text Width 50
Col6=reqCol3 Short
Col7=reqCol4 Text Width 50
Col8=reqCol5 Text Width 50
Col9=reqCol6 Text Width 5

which runs from Query Analyzer, but from the website I get:
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.

I then created a linked server called CSVUpload, to the directory where the csv files exist and changed the query to :
SELECT isnull(a.optCol1, 'default1'),
isnull(a.optCol2,2),
isnull(a.optCol3,'default3'),
cast(a.reqCol1 as varchar(50)),
cast(a.reqCol2 as varchar(30)),
cast(a.reqCol3 as varchar(50)),
cast(a.reqCol4 as int),
cast(a.reqCol5 as varchar(50)),
cast(a.reqCol6 as varchar(50))
FROM csvupload...DataFile#csv a

here again, it works great from QA, but errors from the web site, this time with :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

I have searched Microsoft and done multiple google searches but havent found anything that helps.

Thinking it might be security issues, I temporarily gave full permissions to everyone on the directory where the files are stored, but no difference...

If there is a simple solution that I am just overlooking, then I apologize for wasting bandwidth, but after banging my head for a couple of days, I thought I would throw it out there for everyone... someone in the world must have done similar things, right?

Oh, to add to the confusion, when I initially wrote the code, tuesday, and tested it from the website, it worked. I tested 2 uploads and everything worked perfectly. When I went to do a final test Thursday morning, before pushing to our Live db, the errors started (with no appearant changes to the server).

Any help would be greatly appreciated.
Thanks"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-21 : 09:09:52

See
http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html
and
http://www.mindsdoor.net/SQLTsql/ImportTextFiles.html

If you don't know what the format of the text file is going to be then you should take the latter approach and bcp into a single column staging table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

skclay
Starting Member

1 Post

Posted - 2005-02-21 : 13:33:56

Well, banging my head against the problem long enough finally led me to the solution.

Microsoft articles 814398 and 296711, discuss problems with the Jet engine. My difficulty arose from the vagueness of the error I got, not error number or file locations...

Appearantly, Jet creates a temporary file dsn for the file being accessed through the Linked Server. Here's the wonderful part, it creates it in the directory referenced by the Environmental Variable "Temp" or "TMP", for the user account that the SQL service runs under...

After finding the directory and assigning appropriate rights, the application worked beautifully...

Samuel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources