Format file help on bulk insert.

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)

Format file help on bulk insert.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-15 : 05:58:44

Hi. I have this specs:
Field Name Type Size
Policy no TEXT 8
Registration TEXT 15
Customer name TEXT 30
Customer address TEXT 30
Customer province TEXT 30
Customer telephone TEXT 15
Cover start date DATE 8
Cover end date DATE 8
Policy First issue date DATE 8
Cover code TEXT 3
Vehicle make TEXT 20
Vehicle model TEXT 20
Vehicle year NUMBER 4
Office TEXT 1

I would like to know if it is possible to create a format file for this. I have a problem understanding mostly the Host file data type. I want the date to be YYYYMMDD , how would i format this as i don't see any xtra options. A small example of e lines for date,text and number will be appreciated.
Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-07-15 : 09:43:54

Not sure what you want. Are you looking for how to create a table as the target of the bulk insert?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-15 : 09:50:44

Yes, i have a table with the columns as above and the file.txt
The problem are many.
1)The file is not csv compliant so i has something like "asadsasd asdsda asd12312 asd asd asd" but, from above, i know what the field max length is. I was also asking for the format file that will go to WITH(FORMATFILE='C:\blah\f.xml) in order to somehow manipulate the "/r" or "/t etc, but i guess i can do it outside of the file (into the "WITH")..
As i've read, it cannot be done and i am yet to create a temp table with the complete rows and then do substrings. So can it be done?
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources