CODEWITHSUNDEEP
sql-servercsvimportdelimiter
Kaja
Kaja

Reputation: 3057

Specifying Text qualifier while importing a csv file to SQL Server DB

I have a comma delimited csv file, the problem is that there is a comma also within columns.

TEXT,["pn","service"]

TEXT is in one column and ["pn","service"] the other column.

How can I use text qualifier to separate columns correctly?

enter image description here

Upvotes: 0

Views: 3027

Answers (1)

TEEKAY
TEEKAY

Reputation: 1186

I recently had to deal with this and offer wrapping the fields with an identifier. I modified the macro below to separate by pipe instead of quote. The superuser post has more options. Check out https://support.microsoft.com/en-us/help/291296/procedure-to-export-a-text-file-with-both-comma-and-quote-delimiters-i and https://superuser.com/questions/130592/how-do-you-force-excel-to-quote-all-columns-of-a-csv-file

Updated per @Larnu's suggestions - what I'm using to rewrap some data files prior to importing them for one off loads. Just be careful opening the CSV in excel directly... you might see values changes.

Sub PipeCommaExport()
   ' Dimension all variables.
   Dim DestFile As String
   Dim FileNum As Integer
   Dim ColumnCount As Integer
   Dim RowCount As Integer

   ' Prompt user for destination file name.
   DestFile = InputBox("Enter the destination filename" _
      & Chr(10) & "(with complete path):", "Quote-Comma Exporter")

   ' Obtain next free file handle number.
   FileNum = FreeFile()

   ' Turn error checking off.
   On Error Resume Next

   ' Attempt to open destination file for output.
   Open DestFile For Output As #FileNum

   ' If an error occurs report it and end.
   If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
      End
   End If

   ' Turn error checking on.
   On Error GoTo 0

   ' Loop for each row in selection.
   For RowCount = 1 To Selection.Rows.Count

      ' Loop for each column in selection.
      For ColumnCount = 1 To Selection.Columns.Count

         ' Write current cell's text to file with quotation marks.
         Print #FileNum, "|" & Selection.Cells(RowCount, _
            ColumnCount).Text & "|";

         ' Check if cell is in last column.
         If ColumnCount = Selection.Columns.Count Then
            ' If so, then write a blank line.
            Print #FileNum,
         Else
            ' Otherwise, write a comma.
            Print #FileNum, ",";
         End If
      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
   ' Start next iteration of RowCount loop.
   Next RowCount

   ' Close destination file.
   Close #FileNum
End Sub

Upvotes: 1

Related Questions