1

I want to convert my .xlsx excel sheet file to a .csv file, to export it inside my SQL server 2008 using Import Data wizard. So i follow these steps:-

  1. I open my .xlsx excel sheet inside Microsoft Excel 2010 application.

  2. I click Save As.

  3. I chose CSV file type as follow:- enter image description here

  4. i went to SQL Server >> Import Export Data (64X), as follow:- enter image description here

  5. i browse for the .csv and do the mapping, as follow:- enter image description here

then i continue with the wizard, which created a new Database table for me , but the problem is that inside my original .xlsx file i already have some cell which contain , character, so inside the .csv file it will map the data wrongly , since the , is used to define data separators inside the .csv. so can anyone adivce how i can overcome this problem ?? can i convert my .xlsx file into a valid .csv file, where it will add any , found insde the .xlsx cells as part of the data itself and not as a seperator inside the .csv file ?

Community
  • 1
  • 1
John John
  • 1
  • 72
  • 238
  • 501
  • Possible duplicate of [Dealing with commas in a CSV file](https://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file) – litelite Jul 24 '17 at 16:53
  • 1
    This is why CSV wraps each value in double quotes so that when the CSV is imported the "'s denote values even if a comma exists. and why " must be escaped and no need to worry about the comma. https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv look at the CSV on export in notepad or notepad++ each value is enclosed in double quotes and separated by a `,` right? so comma's don't cause your problem. Double quotes could though. – xQbert Jul 24 '17 at 16:54
  • Just import Excel file directly. Why do you bother converting it to CSV? See this: https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/import-data-from-excel-to-sql – Alex Jul 24 '17 at 19:09
  • @xQbert now if i save as my .xlsx file using the MS Excel program and i chose "CSV (Comma delimited)" from the Save as type, then the generated .csv will not contain any double quotes (unless the excel sheet contain " inside its cells).. the generated .csv file it will only add `,` after each excel sheet cell. and if the cell itself contain `,` then nothing will indicate this.on the other hand if i chose "CSV (MS-DOS)" when i save as my .xlsx file, then as you mentioned the .csv file will wrap any cell which have `,` – John John Jul 24 '17 at 19:41
  • @xQbert with double quotes, but saving my .xlsx file as "CSV (MS-DOS)" will do wrong characters encoding , for example `å` will be saved as `†`,, while saving the .xlsx file as "CSV (comma delimted)" will not have any character encoding problems – John John Jul 24 '17 at 19:42
  • @Alex i tried to directly import the .xlsx file using the import export data wizard but i got this error `The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)`.. – John John Jul 24 '17 at 19:43
  • @johnG, you can download it from MS (https://www.microsoft.com/en-au/download/details.aspx?id=13255). – Alex Jul 24 '17 at 19:50
  • @johnG - one more thing, I assume you are running SSMS on a different machine to SQL Server, as installing this provider on the machine with SQL Server may require a reboot. – Alex Jul 24 '17 at 19:59

0 Answers0