Column getting truncated

[AstridM]

Hello,
I have an macro in Excel that I copied and now it is running ok, but the issue I have that one of the columns gets truncated and does not bring the data after it.

The code is

Sub CSVtoXLS()
'UpdatebyExtendoffice20170814
    Dim xFd As FileDialog
    Dim xSPath As String
    Dim xCSVFile As String
    Dim xWsheet As String
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.StatusBar = True
    xWsheet = ActiveWorkbook.Name
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    xFd.Title = "Select a folder:"
    If xFd.Show = -1 Then
        xSPath = xFd.SelectedItems(1)
    Else
        Exit Sub
    End If
    If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
    xCSVFile = Dir(xSPath & "*.csv")
    Do While xCSVFile <> ""
        Application.StatusBar = "Converting: " & xCSVFile
        Workbooks.Open Filename:=xSPath & xCSVFile
        Call ColumnsSub
        ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xlsx", vbTextCompare), xlWorkbookDefault
        ActiveWorkbook.Close
        Windows(xWsheet).Activate
        xCSVFile = Dir
    Loop
    Application.StatusBar = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub


Sub ColumnsSub()


Dim rag As Range


Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
            Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), _
            Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
            Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), _
            Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), _
            Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
            Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), _
            Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), _
            Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), _
            Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array(67, 1), _
            Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array(72, 1), Array(73, 1), Array(74, 1), _
            Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), Array(79, 1), Array(80, 1), Array(81, 1), _
            Array(82, 1), Array(83, 1), Array(84, 1), Array(85, 1), Array(86, 1), Array(87, 1), Array(88, 1), _
            Array(89, 1), Array(90, 1), Array(91, 1), Array(92, 1), Array(93, 1), Array(94, 1), Array(95, 1), _
            Array(96, 1), Array(97, 1), Array(98, 1), Array(99, 1)), _
            TrailingMinusNumbers:=True


End Sub

I need to convert a csv to excel and do a text to columns.

Since I have commas on my on of the columns as a delimited I used a |

On of the columns is called Prop 65 Warning and the text inside is

"This product can expose you to chemicals including di(2-ethylhexyl)phthalate, which are known to the State of California to cause cancer and birth defects or other reproductive harm. For more information go to www.P65Warnings.ca.gov."

But when I run the process I only get This product can expose you to chemicals including di(2-ethylhexyl)phthalate

And nothing after that, not only the column gets truncated, but the rest come back empty, not sure what I am doing wrong.

Thanks
Astrid

[snb]

This should be sufficient:

Sub M_snb()
  c00 = Application.DefaultFilePath & "sample.csv"
    
  With Application.FileDialog(3)
    .initialfilename = "*.csv"
    If .Show Then
      c01 = .SelectedItems(1)
      With CreateObject("scripting.filesystemobject")
        .createtextfile(c00).write Replace(.opentextfile(c01).readall, ", w", ". w")
      End With
           
      Workbooks.Open c00
    End If
  End With
End Sub

[AstridM]

Thanks, but I am not sure where to add that. before I do the text to columns?
I am trying to convert 180 files.

[snb]

You don't need the 'texttotcolumns'. My code replaces all the code you had. The code I provided uses just another method. Just try it once, than we can look at the next step.

[AstridM]

I am a bit confused, but then again, I am pretty new at this.
I ran the code that you sent and save the file as a csv and then I ran the old code and it worked.
When I tried to join them, it opens the file but it does not do the text to columns I need.

We have a large number of files, that I need to do the text to column and convert to excel.

[snb]

You didn't run the whole code I provided and only that code.

[AstridM]

Good morning, I did run the code you have provided.

[snb]

Then you must have noticed that the csv-file you chose in the dialogwindow has been adapted and been opened correctly, without any 'truncation'.
If all data are still in column A, you should use:

Sub M_snb()
  c00 = Application.DefaultFilePath & "sample.csv"
    
  With Application.FileDialog(3)
    .initialfilename = "*.csv"
    If .Show Then
      c01 = .SelectedItems(1)
      With CreateObject("scripting.filesystemobject")
        .createtextfile(c00).write Replace(.opentextfile(c01).readall, ", w", ". w")
      End With
           
      Workbooks.Open c00 local:=true
    End If
  End With
End Sub