Load Text File into Table - Seperated by Commas

Question

Post reply

Load Text File into Table - Seperated by Commas

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 18, 2012 at 10:02 am

#272883

I have this file and I need to load it into a single column TableName in a new Table.
It is AS400 Data. The system is hosted by someone else so I do not have system catalogue privileges.
If I import it into Excel it brings each value as a separate column.
The text file looks this:
ASCKERT, ASSURTY, BYBUTSX,ABDITCS, etc
PMOUIYT, SDERTYU...
JUAPUYT, HJUYIRC...
Any ideas would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

More details. Not sure what you are actually looking for when processing of the file is completed.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

The items listed are table names.

I need to remove the comma and replace it with a line feed to that each table appears on a separate row.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

You know with over 3,000 points you really should know better than posting such vague questions. Try reading the article at the first link in your signature.

Then when you want to figure out how to parse a comma delimited string, take a look at the second link in my signature.

_______________________________________________________________

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/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I would have included the actual file but the table names are confidential.

I guess you know what I needed or you would not suggest that I use the splitter.

Have a nice day.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 5

Hi Welsh Corgi,

I did a search on Google (Search term: Repace comma with line feed in text file) and came up with the following:

There are a couple of solutions. This requires a text editor with more features than Notepad, preferably "regular expressions".

1) Get a free download of Notepad++. After pasting your text into a new file window, select Search / Replace from the menu. Put a comma in the "Find what:" box, and the following in the "Replace with:" box:

NOTE: You must select the radio button at the bottom left of the replace popup for "Regular expression". The above will execute a regular expression to replace all commas with a carriage return.

2) The same thing can be done with Edit Pad Pro, Ultraedit, etc. Another idea is to view the file in hexadecimal code (Editpad Pro should be able to do this), do a find for the hex value of a comma (2C), and do a "replace all" with the hex value of a carriage return (0D).

I've not tried any of the above so can't couch for any of the solutions. Best of luck!

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

Jared SSC Guru Points: 61793 More actions · Answer 6

Welsh Corgi (4/18/2012)
I have this file and I need to load it into a single column TableName in a new Table.
It is AS400 Data. The system is hosted by someone else so I do not have system catalogue privileges.
If I import it into Excel it brings each value as a separate column.
The text file looks this:
ASCKERT, ASSURTY, BYBUTSX,ABDITCS, etc
PMOUIYT, SDERTYU...
JUAPUYT, HJUYIRC...
Any ideas would be greatly appreciated.

Quick thought without researching it... Can't you "TELL" bcp or import wizard to treat comma as a new line?

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 7

Ya, just verified it. Change your row delimiter to , instead of {CR}{LF} and your done.

Jared
CE - Microsoft

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Thanks for the all of ideas.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/