Please help with the Bulk Insert with substring

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Please help with the Bulk Insert with substring

Author

Topic

nt2701
Starting Member

2 Posts

Posted - 2015-02-03 : 15:36:06

Hi there, please help a newbie here. I need to use the BulK insert function to read a text file and insert the content into a table.
The text file doesn't have any delimiter, something like this:

0001HereistheTitleProductIDProductPrice
0002HereistheTitleProductIDProductPrice
0003HereistheTitleProductIDProductPrice

and so on.
I'd need to insert the 1st 4 character for the ID, Title, Product ID and Product Price.

What would I use for the FIELDTERMINATOR ?
Greatly appreciate your help.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 15:41:21

you need a format file: https://msdn.microsoft.com/en-us/library/ms178129.aspx

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-02-04 : 06:42:52

For bulk you would be better cleaning the data up before copying across, use a batch file or copy to Excel and use function to split. Then use bulk insert, something like:

BULK INSERT #CSVTest
FROM 'c:\csvtest.txt'

WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

GO

Personally Id just pull it in and use SQL to clean, use LEFT to get number of chars

select LEFT('0003HereistheTitleProductIDProductPrice', 4)

Result:
0003

We are the creators of our own reality!

nt2701
Starting Member

2 Posts

Posted - 2015-02-04 : 09:41:54

Thanks gbritton and sz1. Appreciate your help.
The client prefers the original text file to be intact, not to be modified in anyway.

sz1, I might need to go the route that you suggested, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources