CODEWITHSUNDEEP
sqlsql-serverbatch-filetext
andy91
andy91

Reputation: 157

SQL to import certain comma separated fields from a text file

I'm trying to import data from a number of text files into a SQL-Server Database.

I'm happy with the batch side of running SQL on all the files, but I'm struggling to get the SQL to work.

The format of the text files is:

UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT
UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT
UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT

Field1,Field2,Field3,Field4

UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT
UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT
UNWANTED TEXT UNWANTED TEXT UNWANTED TEXT

The complication is the fields in the text file do not match up in order with the table fields, and I don't want to import all the data.

My table looks like:

ID (int - autoincrement) | Field3 (nvarchar) | Field1 (datetime) | ImportDate (datetime) | Text from whole text file for reference (nvarchar)

Therefore my requirements are for Field 3 to be placed in Column 2 of the table and Field 1 to be placed into Column 3 of the table.

My SQL looks like:

USE FOO_DB
BULK INSERT dbo.BarTable
WITH
(
    FORMATFILE = 'C:\format.txt'
    FIELDTERMINATOR = ',',
    FIRSTROW = 13
)

My format.txt file looks like:

10.0
5
1       SQLCHAR             0       24      ","    3        Field1                        Latin1_General_CI_AS
2       SQLCHAR             0       100     ""     0        ID                            ""
3       SQLCHAR             0       100     ","    2        Field3                        Latin1_General_CI_AS
4       SQLCHAR             0       100     ""     0        ImportDate                    ""
5       SQLCHAR             0       24      ""     0        FileText                     ""

When attempting to import, I get:

0 rows affected

If I fiddle around the the FIRSTROW value, I occasionally get loads of:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (Field 3)

I read that FIRSTROW doesn't count from the first row of the actual file, but the first row that contains field separators? If so, the only row before my data that has commas is row 4. My data is on row 13 - there are no rows inbetween with commas.

If I delete all the data in the text file apart from the comma separated stuff I want, then adjust FIRSTROW to 1, I get:

Bulk load: An unexpected end of file was encountered in the data file
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Upvotes: 0

Views: 1279

Answers (1)

John Bell
John Bell

Reputation: 2350

You'd be much better off importing the data into a staging table, then re-arranging your columns with a simple SELECT INTO statement.

Upvotes: 1

Related Questions