0

I am downloading CSV files which are comma-separated. The problem i'm having is that the commas are screwing-up my import into a database table (SQL Server). For example, I have a header row called hotel_name, but some of the names are like the following:

HOTEL_NAME
hilton
cambridge,the

The problem is that fields containing a comma in the hotel name will move to the adjacent column, like this I'm wondering if converting from CSV to a pipe-delimited format will work.

The problem i'm having is that i'm not sure how to get started. I've tried following the Powershell documentation but get basic errors. I think this is because i'm new to Powershell and not understanding something. Can someone please post a script of how to change the comma-separated file to a pipe-delimited file?

Sorry if this is confusing, i'm finding the formatting on StackOverflow to be a bit crazy.

SQL User
  • 13
  • 1
  • 5
  • It doesn't seem powershell will help you. In the CSV it's not like you have some metadata to keep the entries grouped. What keeps the entries assigned to specific headers are commas. Same for powershell: it's able to recognize and parse your data because it assumes the coma separates specific entry assigned to a specific header. If you start replacing commas with pipes you will run into the same issue. Only now the name from your example will be `cambridge|the`. – PiotrWolkowski Feb 19 '15 at 21:11
  • PowerShell can read or `IMPORT-CSV` then perform an operation on that data. As an example, you could use Regular Expressions to find characters then skip as needed and finally replace with another character. More reading: http://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file or you could check if Excel can import the CSV and treat consecutive delimiters as one. – user4317867 Feb 19 '15 at 21:44
  • possible duplicate of [Commas within CSV Data](http://stackoverflow.com/questions/4123875/commas-within-csv-data) – user4317867 Feb 19 '15 at 22:17

1 Answers1

0

Taken from Dealing with commas in a CSV file

Use " to wrap data that contains a comma.

For example Server000,"Microsoft(R) Windows(R) Server 2003, Enterprise Edition"

Community
  • 1
  • 1
user4317867
  • 2,397
  • 4
  • 31
  • 57