Import Text File to SQL Database

Question

Import Text File to SQL Database

db2mo

Ten Centuries

Points: 1341
More actions
September 12, 2013 at 10:08 am

#298653

I have a text file with no delimiters and fixed width fields that I need to import into a SQL Server database. An example of the text file is:
012345678901012013TYPEADESC FIELD 8.00
123456789001022013TYPEBDESC FIELD 14.00
234567890101032013TYPECDESC FIELD .75
Text File is set up as
EmpID: 10
Date: 8 (mmddyyyy)
Type: 5
Desc: 12
Hours: (6,2) Decimal
SQL Server table is set up as:
Date (datetime)
Type: varchar(5)
Desc: varchar(12)
Hours: decimal(6,2)
EmpID: varchar(10)
The problem I'm having is with the Date field. How do I convert the date from the text file (mmddyyyy) to the sql server date field (yyyy-mm-dd hh:mm:sss)?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

db2mo (9/12/2013)
I have a text file with no delimiters and fixed width fields that I need to import into a SQL Server database. An example of the text file is:
012345678901012013TYPEADESC FIELD 8.00
123456789001022013TYPEBDESC FIELD 14.00
234567890101032013TYPECDESC FIELD .75
Text File is set up as
EmpID: 10
Date: 8 (mmddyyyy)
Type: 5
Desc: 12
Hours: (6,2) Decimal
SQL Server table is set up as:
Date (datetime)
Type: varchar(5)
Desc: varchar(12)
Hours: decimal(6,2)
EmpID: varchar(10)
The problem I'm having is with the Date field. How do I convert the date from the text file (mmddyyyy) to the sql server date field (yyyy-mm-dd hh:mm:sss)?

You will have to bring the data into a staging table first, then convert that into a datetime.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

db2mo Ten Centuries Points: 1341 More actions · Answer 2

What data type do I bring it into the staging table as?

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Probably char(8). Then you will have to do some substring manipulaton to separate the date parts.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

db2mo (9/12/2013)
What data type do I bring it into the staging table as?

If you use a BCP format file, You can bring it into a staging table as the correct datatype.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

db2mo Ten Centuries Points: 1341 More actions · Answer 5

I was able to get the data into a staging table in SQL. All the fields of the staging table are varchar except for the Hour field which is decimal. Now, what substring conversion would I use to get the mmddyyyy varchar field converted to datetime?

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

This should work.

declare @Date char(8) = '09132013'

select cast(RIGHT(@Date, 4) + LEFT(@Date, 4) as datetime)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

db2mo Ten Centuries Points: 1341 More actions · Answer 7

That worked! Now this is probably a silly question, but how do I get it to use the date that's already in the date field? By using the statement above, it converts all dates, but it gives them that specific date. How do I get it to use the actual date in the "Date" column?

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

db2mo (9/17/2013)
That worked! Now this is probably a silly question, but how do I get it to use the date that's already in the date field? By using the statement above, it converts all dates, but it gives them that specific date. How do I get it to use the actual date in the "Date" column?

Modify the statement to use the column instead of a variable.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

db2mo Ten Centuries Points: 1341 More actions · Answer 9

This is my code:

declare @Date varchar(8) = 'UnconvertDate'

select CAST(RIGHT(@dATE, 4) + LEFT(@Date, 4) as date)

from hp27.importData

I get this error message:

Conversion failed when converting date and/or time from character string.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

db2mo (9/19/2013)
This is my code:
declare @Date varchar(8) = 'UnconvertDate'
select CAST(RIGHT(@dATE, 4) + LEFT(@Date, 4) as date)
from hp27.importData
I get this error message:
Conversion failed when converting date and/or time from character string.

You don't want a variable at all here. You certainly can't convert that string literal to a datetime.

Try this:

select CAST(RIGHT(UnconvertDate, 4) + LEFT(UnconvertDate, 4) as date)

from hp27.importData

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/