Bulk Insert - Format File - SQL 2005 Express

[mapman04]

I am trying to import data to a table and need some help. After researching a bit, I decided to use BULK INSERT and a format file to import the data.

The data is in a comma delimted file with the " marks as a text qualifier. I'm not having any luck importing the data so I made a table with a single column set as a VARCHAR(50) for testing. I made a format file which contains:

9.0
1
1 SQLCHAR 0 4 "\r\n" 1 fldTrans ""

My test data file has 1 column and looks like this:

"RED"
"BLUE"

When I execute my procedure and it says it's completed successfully. The code is:

BULK INSERT tblTest
FROM 'F:\Files\Data.Txt'
WITH (FORMATFILE = 'F:\Files\Data.Fmt')

Is there any easier way to get data into SQL 2005 Express? Am I on the right track?

Thanks in advance,

mapman04

 

[mapman04]

I forgot to put that although it says the procedure was completed successfully, the table does not have any records in it.

Thanks,

mapman04

 

[broni108]

Hi Mapman04

I'm experiencing similar problems now. I'm trying to import a text file as a new table. Fields are delimited by the | symbol. Just wondering whether you ended up finding a solution to your problem?

Kind regards,

broni108

 

[mapman04]

I was able to get it to work. I ended up using Access to export the data without the quote marks to a text file. I then created a stored procedure and use the bulk insert and FMT file to import it. It's not the easiest way to import the data, but it works. Here's my FMT file as a reference.

9.0
17
1 SQLCHAR 0 6 "," 1 fldFiscalPD ""
2 SQLCHAR 0 4 "," 2 fldFiscalYR ""
3 SQLCHAR 0 10 "," 3 fldType ""
4 SQLCHAR 0 40 "," 4 fldDate ""
5 SQLCHAR 0 20 "," 5 fldAmount ""
6 SQLCHAR 0 6 "," 6 fldDBCR ""
7 SQLCHAR 0 6 "," 7 fldUser ""
8 SQLCHAR 0 20 "," 8 fldMO ""
9 SQLCHAR 0 6 "," 9 fldMOLine ""
10 SQLCHAR 0 20 "," 10 fldParent ""
11 SQLCHAR 0 20 "," 11 fldQuantity ""
12 SQLCHAR 0 20 "," 12 fldComponent ""
13 SQLCHAR 0 20 "," 13 fldBin ""
14 SQLCHAR 0 6 "," 14 fldIR ""
15 SQLCHAR 0 30 "," 15 flrAccount ""
16 SQLCHAR 0 4 "," 16 fldPlant ""
17 SQLCHAR 0 10 "\r\n" 17 fldSort ""

Hope this helps.

mapman04