-1

I have phone number as ###-###-#### in my SQL table. When I export it to CSV file. It shows as turns into a value of the match operation, ie, treating - as a subtract sign.

Also, as an alternative, I can convert ###-###-#### as ##########, ie, removing - how to do that in select query.

Thanks

I tried to cast (phone as varchar) but that didn't help.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Curly Shaan
  • 3
  • 3
  • 3
    CSV files don't do math. Are you opening the CSV file in Excel? – Frank Ball Mar 26 '19 at 14:58
  • 2
    CSV files don't have a concept of mathematical operators. Your problem is related to how you're opening / reading it. If you did do the conversion, and the phone number started with a 0, whatever you're doing to open the file would likely lose the leading zero. Fix the actual problem, not the superficial symptom. *(For example, import the csv file in such a way that the column is correctly treated as a string.)* – MatBailie Mar 26 '19 at 14:59
  • For that matter, CSV files don't have a concept of *numbers*; it's a stringly-typed format. The actual question here seems to be “How do I force Excel to treat a particular column as text when I open a CSV filet?” – dan04 Mar 26 '19 at 15:01
  • @FRANKBALL Yes I am opening in Excel. – Curly Shaan Mar 26 '19 at 15:02
  • @jarlh im doing in dermio so i guess SQL server. – Curly Shaan Mar 26 '19 at 15:03
  • As others explained, CSV files are just strings. In Excel you can change the style of a column to text so it isn't treated as a formula. Or, you can export your CSV file using text qualifiers (typically `"`) to tell any program that the field is a text, not a number – Panagiotis Kanavos Mar 26 '19 at 15:03
  • @CurlyShaan in SQL Server you can easily export data into Excel files directly using SSIS or the Export Data wizard. That wizard creates an SSIS package that can be reused – Panagiotis Kanavos Mar 26 '19 at 15:04
  • @CurlyShaan How do you export the file? Any method you use to export the CSV file from SQL Server should have a way to add text qualifiers. – Panagiotis Kanavos Mar 26 '19 at 15:04
  • Hi, all I am obviously not an expert and I use GUI of dermio to export the file as a CSV ( not excel format available ) and I am honestly not sure what SQL but I am sure it is not Oracle. I am not sure to say my DBMS is SQL or SQL Server. – Curly Shaan Mar 26 '19 at 15:07
  • @PanagiotisKanavos let me read about text qualifiers. – Curly Shaan Mar 26 '19 at 15:08
  • @PanagiotisKanavos so if I add select "Phone_Num" from ----- when I export it the phone_num column is treated as text? – Curly Shaan Mar 26 '19 at 15:10
  • No. How do you export the file? The field/row delimiters, text qualifiers are all parameters of whatever method you use to generate a CSV file – Panagiotis Kanavos Mar 26 '19 at 15:11
  • @PanagiotisKanavos CREATE TABLE "__datasetDownload"."a19fbcb4-0b86-4efd-9e90-a7a80f995b21" STORE AS (type => 'text', fieldDelimiter => ',', lineDelimiter => ' ') WITH SINGLE WRITER AS SELECT * FROM ( SQL QUERY what i write) this is what's happening – Curly Shaan Mar 26 '19 at 15:16
  • If you want to remove a char from a column you can simply [replace](https://learn.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql?view=sql-server-2017) with Nothing. – xdtTransform Mar 27 '19 at 06:59

1 Answers1

2

Instead of opening the .csv file in Excel, use Excel to import the file. Go to the "Data" ribbon and click the "From Text/CSV" button. Select the file you exported, click the "Load" button and it should import the data from the CSV file with the phone # intact. I ran a test on a .csv file that has phone #s in it and it worked like a charm.

Frank Ball
  • 1,039
  • 8
  • 15
  • On Excel 2010: 1. Data > From Text > Choose .csv file 2. Original Data type - preview showed comma separated - chose Delimited. 3. Delimiters - Comma 4. Column data format - choose each column to show numbers as a string in and pick 'Text' radio button 5. Finish – Amos Oct 27 '20 at 08:54