Solved

How to Add Unpredictable CSV Files to SQL via Bulk Copy

Posted on 2011-02-11
3
470 Views
Last Modified: 2012-05-11
I need to import about 600 different CSV files from different sources and then begin normalizing the data.

What I want to do is import them using Bulk Copy into files like:

Table 1
  f1 varchar(200),
  f2 varchar(200),
  f3 varchar(200),
  ...
  f100 varchar(200)

Once the files are imported, then I can run utilities in CF to show me the first 10 rows, let me figure out which column is really the first name, the last name, etc., and then put together the mappings to place it all into 1 consolidated table

My problem is the bulk copy ...

What I need to do with a SQL statement is:

1 - Create the import table with 100 columns (I can do this)
2 - Issue the bulk copy against the file, such as  c:\products1.csv
     (note: some are CSV, some are TSV, but that's a delineater issue)
3 - Have it import

I have seen examples like this:

BULK
INSERT CSVTest
FROM 'c:\testfile.tsv'
WITH
(
FIRSTROW = 1,
MAXERRORS = 999999,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
GO

It fails with errors:

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

(by coincidence, the Database I created to do the bulk imports is named BULK).

I'm fully aware that the data may have errors and inconsistent formatting between CSV files.

The question is this:  what's the best way to "just get the data in there" into SQL, and then I can clean it up in phase 2 once it is in the temp table?

I'm running MS SQL 2005, and I need to do this with SQL statements.  I have hundreds of files to import and will be using a separate box running Cold Fusion to find the files to import and initiate a CFQuery to run the query on the SQL server.

Doing the wizard 600 times is not an option...

Thanks
0
Comment
Question by:drgdrg
3 Comments
 
LVL 15

Accepted Solution

by:
MohammedU earned 500 total points
ID: 34876177
You can try openrowset option...
SELECT * INTO thetable FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=D:\databases;Extensions=CSV;', 'SELECT * FROM thefile.csv')

Check the following threads to find the usage...
http://www.databasejournal.com/features/mssql/article.php/10894_3331881_2/OpenRowSource-and-OpenRowSet-in-SQL-Server-2000.htm
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/5869d247-f0a0-4224-80b3-ff2e414be402
0
 
LVL 2

Expert Comment

by:MTillett
ID: 34886426
Can you post the statement that CF is firing at SQL Server and do you have any influence over it?  Naming your database BULK was an interesting choice, given that it's a reserved word :-)
0
 
LVL 1

Author Closing Comment

by:drgdrg
ID: 35074530
Will look into these examples.  Thanks
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using this function 4 38
Trigger C# code inside the SQL Server 6 26
Access #Deleted data 20 37
SQL Distinct Question 3 12
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question