Importing a text file into SQL Server

Question

Importing a text file into SQL Server

itmasterw 60042

SSCrazy

Points: 2858
More actions
April 3, 2015 at 7:40 am

#295642

Hi,
Someone was telling me that they had a text file that was both comma and piped delimited and that they used BCP to import it.
While they did not remember the exact details, they said that first it converted it to XML and then used bcp to import it.
Can someone explain how this is done; I cannot find anything on the web about it.
Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

itmasterw 60042 (4/3/2015)
Hi,
Someone was telling me that they had a text file that was both comma and piped delimited and that they used BCP to import it.
While they did not remember the exact details, they said that first it converted it to XML and then used bcp to import it.
Can someone explain how this is done; I cannot find anything on the web about it.
Thank you

Looks to me that some external tool must have been used for the text to xml conversion, don't have a recollection of such tool within the SQL Server stack.

😎

On the problem of importing multi-delimiter files, normally the delimiters are hierarchical, that is there is a precedence i.e. in this case it could be the comma as the higher level and pipe as the lower.

Input String ---> "ABC,DEF,GHI,J|K|L"

| | | | | |

(1) +--------------+ | | | | | |

,----+-| Header Table | | | | | | |

| +--------------+ | | | | | |

| | Column 1 |<------------' | | | | |

| +--------------+ | | | | |

| | Column 2 |<----------------' | | | |

| +--------------+ | | | |

| | Column 3 |<--------------------' | | |

| +--------------+ | | |

| | | |

| (0-n)+--------------+ | | |

'----0<| Detail Table | | | |

+--------------+ | | |

| Column 1 |<-----------------------' | |

+--------------+ | |

| Column 2 |<-------------------------' |

+--------------+ |

| Column 3 |<---------------------------'

+--------------+

Importing this type of files is straight forward using a staging environment where the data is loaded using the highest level delimiter and then the appropriate columns are split on the lower level delimiters in the right order.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

itmasterw 60042 (4/3/2015)
Hi,
Someone was telling me that they had a text file that was both comma and piped delimited and that they used BCP to import it.
While they did not remember the exact details, they said that first it converted it to XML and then used bcp to import it.
Can someone explain how this is done; I cannot find anything on the web about it.
Thank you

There's no need to jump through all those hoops if the delimiters are solid. Just create a BCP format file and you're done. It only takes a couple of minutes to create such a thing.

It would be helpful to know more about the file you're trying to import because there are a couple of "gotchas" with BCP. For example, the header row must have exactly the same delimiters as the body rows in order to successfully be able to skip the header rows.

--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)