1

I use the following VBA code to open CSV files in Excel. Most of the time the information from the CSV file is put into columns. However, this is not always the case. Sometimes the data from the CSV file is not put into columns, but just one column. How can I make sure the data from the csv file is always put in columns?

Sub OpenFileBox()

    Application.ScreenUpdating = False

    Dim FileName As String, test As Workbook
    Dim shActive As Worksheet

    FileName = Application.GetOpenFilename("Comma Separated Values,*.csv", , "Browse for workbook")

    Set shActive = ActiveSheet
    Set test = Workbooks.Open(FileName)

    test.Sheets(1).UsedRange.Copy shActive.Range("A1")

    test.Close SaveChanges:=False

    Exit Sub

End Sub

CSV file that works: enter image description here

CSV file that doesn't work: enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
TAH.
  • 11
  • 5
  • Is the problem restricted to specific files? If you look at the content of one of those files in (eg) notepad dies it look like a valid CSV file? – Tim Williams Jan 06 '20 at 19:54
  • Are the .csv files in discussion 'comma delimites' for sure? How do they look if opened in Notepad/Wordpad? – FaneDuru Jan 06 '20 at 20:09
  • I checked 2 CSV files, one for which the macro puts the data into columns, and one for which it doesn't. Both files look like a normal CSV file. The only difference is that the file which works fine shows the following character double " " . The file which doesn't work shows one of the following character: ". – TAH. Jan 06 '20 at 20:10
  • Yes, both files are comma seperated values files – TAH. Jan 06 '20 at 20:10
  • Would help to show sample content from one of the non-working files. We can't help much without seeing the actual problem. – Tim Williams Jan 06 '20 at 20:13
  • This CSV file works: "2019-12-1","2","1","2.68","0.00","0.00","12.00","12.00","0.00" This CSV file does not work: "2019-12-1,""2"",""1"",""2.68"",""0.00"",""0.00"",""12.00"",""12.00"",""0.00""" – TAH. Jan 06 '20 at 20:15
  • I added screenshots from the CSV files to the original question as well. FYI: both CSV files were downloaded from the same location but on a different laptop. – TAH. Jan 06 '20 at 20:25
  • The non-working example is not a valid CSV (at least not for your expect multi-column format). Each line begins with a single double-quote character but there's no matching closing quote for that first field: doubled-up quotes are used to escape quotes in field content - https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv. If you download those two files on the same laptop are they still different? – Tim Williams Jan 06 '20 at 20:41
  • Ok! That makes sense. Actually, both file are the same, downloaded from the same source with the same data, but downloaded on a different laptop. – TAH. Jan 06 '20 at 20:52
  • That second one is valid, it's just that it's a single column. Each double quote inside that single column is escaped. It's actually being put correctly into excel. – JNevill Jan 06 '20 at 21:25
  • Thanks! However, I would like Excel to put the files into columns each time. Is that possible? – TAH. Jan 06 '20 at 21:29
  • 1
    How exactly are the files being downloaded? – Tim Williams Jan 06 '20 at 21:40
  • They are downloaded from WooCommerce (an open source e-commerce platform). The download shows sales for a given time period – TAH. Jan 06 '20 at 21:51
  • Downloaded using a web browser? – Tim Williams Jan 06 '20 at 22:19
  • You could do a cells.replace **after** the workbook opens and then iterate through each line and split into columns. – Galimi Jan 06 '20 at 22:35
  • As @TimWilliams implies, there has to be something different about the way the files are being downloaded/generated on the computer where it results in a single column. The way the double quotes are incorporated, all of the commas are **within** quoted fields so will not act as field separators. You need to look at the **specifics** of the download process on the laptop where your download results in a single column. – Ron Rosenfeld Jan 06 '20 at 22:45
  • 1
    Quick fix - open the file in Notepad and replace all the " with nothing. As long as your field values do not contain any commas that should work fine. – Tim Williams Jan 06 '20 at 23:02

0 Answers0