SQL 2008 r2 Importing Text Files

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

SQL

I have daily files that need to be appended to the same sql table. I know how to use the "SQL Server Import and Export Wizard" but I need to add the "file name" and "date/time" to the end of each daily file I import(...so we can distinguish the records as to when and which file they came from).

Question 1: Is the "SQL Server Import and Export Wizard" the best way to import these files?

Question 2: How do I add the File Name and the Import Date/Time the imported file? Note: I will be append the daily files to the table.

The is usually very little on the web or so much it's like asking someone what time it is and they tell how to build a watch. :)

Posted 22-Nov-13 5:34am

Runner55Code

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-11-22T05:56:00

Solution 1

Try to use OPENROWSET[^].

SQL

DECLARE @tname VARCHAR(150)
DECLARE @fname VARCHAR(150)

SET @tname = 'c:\test\File1.txt'
SET @fname = 'c:\test\File1.fmt'


INSERT INTO SQLTable (Field1, Field2, ..., FileName, ImportDate)
SELECT a.Field1, a.Field2, ..., @tname AS FileName, GETDATE() AS ImportDate
FROM OPENROWSET( BULK , @tname, FORMATFILE = @fanme) AS a;

Please, visit above link get more details ;)

Posted 22-Nov-13 5:56am

Maciej Los

Comments

Runner55Code 22-Nov-13 12:01pm

Wow...excellent. Thanks

Maciej Los 22-Nov-13 12:04pm

You're very welcome ;)
Please, accept it as an answer (green button).

Runner55Code 22-Nov-13 13:22pm

DECLARE @tname VARCHAR(150)
DECLARE @fname VARCHAR(150)

SET @tname = 'C:\DataImport'
SET @fname = 'ImportMe.csv'

INSERT INTO TryTest
(
RecordDate,
EmailAddress,
NameFirst,
NameLast,
Addr1,
Addr2,
AddrCity,
AddrState,
AddrZip,
Phone,
Production,
AdvisorType,
PlacementID,
currentFileName,
ImportDate
)
SELECT
[Record Date],
[Email Address],
[First Name],
[Last Name],
[Street Address],
[Address 2],
City,
State,
[Zip/Postal Code],
PHONE,
Production,
[Advisor Type],
[Placement ID],
@Fname AS FileName,
GETDATE() AS ImportDate
FROM OPENROWSET( BULK , @tname, FORMATFILE = @fanme) AS a;

It doesn't like the comma after BULK and it doesn't find and of the field names after the Select.

Msg 207, Level 16, State 1, Line 26
Invalid column name 'Record Date'.
Msg 207, Level 16, State 1, Line 27
Invalid column name 'Email Address'.
Msg 207, Level 16, State 1, Line 28
Invalid column name 'First Name'.
Msg 207, Level 16, State 1, Line 29
Invalid column name 'Last Name'.
Msg 207, Level 16, State 1, Line 30
Invalid column name 'Street Address'.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'Address 2'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'City'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'State'.
Msg 207, Level 16, State 1, Line 34
Invalid column name 'Zip/Postal Code'.
Msg 207, Level 16, State 1, Line 35
Invalid column name 'PHONE'.
Msg 207, Level 16, State 1, Line 36
Invalid column name 'Production'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'Advisor Type'.
Msg 207, Level 16, State 1, Line 38
Invalid column name 'Placement ID'.

What am I doing wrong.

Maciej Los 22-Nov-13 13:29pm

The column names must be equal (INSERT and SELECT section). Please see the online documentation to properly set OPENROWSET options for csv files.

Runner55Code 22-Nov-13 13:36pm

Will do. Sometimes they instructions on the web can be very confusing. I wish I knew where to go that would explain all the { [ etc. Sometimes simple examples go along way!