How to import .csv file to excel correctly using VBA?

0

I have a .csv file that it opens on excel fine, columns and everything normally but it i use another excel .xlsx file adding a command button with vba code that imports the .csv file, it copies all the columns together and only separates them by semicolons. The strange thing is that the .csv file if I open it normally in excel, from my files, it does not come out separated by semicolon it shows completely file. I used this video https://www.youtube.com/watch?v=EzUTK5hhjWM and change the DataType but actually my .csv file is does not open on a NotePad. It opens on excel perfectly, but if i wanna import it from another excel file it copies it wronly 

Answer
Discuss

Discussion

Side note on opening a CSV with Notepad: Right-click the file > Open with > Notepad

Then you can look at it outside of Excel.

Give Variatus' answer a go and if that doesn't work, upload a sample of your data. Between his method and the one I showed in the video, those are going to be the easiest ways to import (it can get WAY more complex)
don (rep: 1969) Apr 13, '21 at 7:47 pm
thank you, actually it is separed by semicolon if i opened on a notepad but i already used semicolon on my vba code and it doesnt work
martellsara Apr 13, '21 at 8:04 pm
hey already found the problem. thank you 
martellsara Apr 14, '21 at 12:25 am
If you have a solution, please share it here so future readers can better sovle their issues as well :)
don (rep: 1969) Apr 14, '21 at 9:45 am
yesss, i used 
Sub ImportText()
  Dim fileToOpen As Variant
  Dim fileFilterPattern As String
  Dim wsMaster As Worksheet
  Dim wbTextImport As Workbook
  '
  Application.ScreenUpdating = False
    fileFilterPattern = "Text Files (*.txt; *.csv),*.txt;*.csv"
  fileToOpen = Application.GetOpenFilename(fileFilterPattern)
  If fileToOpen = False Then
    MsgBox "No file selected."
  Else
    Workbooks.Open FileName:=fileToOpen, UpdateLinks:=0, Local:=True
    Set wbTextImport = ActiveWorkbook
    Set wsMaster = ThisWorkbook.Worksheets("Base de Datos")
    wsMaster.Rows("2:" & Rows.Count).ClearContents
    wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A2")
    wbTextImport.Close False
  End If
  Application.ScreenUpdating = True
End Sub


i just used Local:=true 
martellsara Apr 14, '21 at 10:31 am
Add to Discussion

Answers

0

The question answered on this folrum must be on this forum. Your reference to a YourTube site isn't useful for that reason. However, below is simple code for importing a CSV file into Excel.

Sub ImportCSVFile()

    Dim Ws As Worksheet
    Dim FileName As String

    Set Ws = ActiveWorkbook.Sheets("Sheet1")    ' change to suit

    FileName = Application.GetOpenFilename("Text Files (*.csv),*.csv", , _
               "Please select source file")

    With Ws.QueryTables.Add(Connection:="TEXT;" & FileName, _
                            Destination:=Ws.Range("A1"))       ' change to suit
         .TextFileParseType = xlDelimited
         .TextFileCommaDelimiter = True
         .Refresh
    End With
End Sub

Actually, there are about 20 properties required and only 2 of them are set in this code. All the others will be default values taken from the system. Some of these defaults are, in fact, not system defaults but values last specified by the user in a similar import carried out manually. This is how the same code can work differently on different systems.

At this link they use an entirely different method. They simply open the file because, as you point out, Excel can do that without any extra help. But they also highlight that CSV is short for Comma Separated Values. Your file appears to use semicolons for separators and that problem is solved by the author. It may be useful to you.

If none of the above helps in your case please edit your question and  include your code.

Discuss

Discussion

Sub ImportText()
    Dim fileToOpen As Variant
    Dim fileFilterPattern As String
    Dim wsMaster As Worksheet
    Dim wbTextImport As Workbook
    Application.ScreenUpdating = False
    fileFilterPattern = "Text Files (*.txt; *.csv),*.txt;*.csv"
    fileToOpen = Application.GetOpenFilename(fileFilterPattern)
    If fileToOpen = False Then
        MsgBox "No file selected."
    Else
        Workbooks.OpenText _
            FileName:=fileToOpen, _
            StartRow:=2, _
            DataType:=xlDelimited, _
            Other:=True, _
        semicolon:=True
                Set wbTextImport = ActiveWorkbook
        Set wsMaster = ThisWorkbook.Worksheets("Base de Datos")
        wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A3")
        wbTextImport.Close False
 End If
    Application.ScreenUpdating = True
End Sub


martellsara Apr 13, '21 at 7:58 pm
That is the code 
I think my .csv file is more an .xlsx file saved as a .csv file but i cant change that because thats how my bad  software downloads it. 
As you can see i changed it to semicolon. But the same thing, opening my .csv file that i think is actually a .xlsx file because is not separed by commas or semicolon, it looks fine but every time i use the command button from another .xlsx file imports it everything in one column and separed by semicolon.
I dont know how this question - answers page works but i really need this to work out so i could send you the files lol but im grateful just for you to help me here.
p.s: Sorry but english is my second language. 
martellsara Apr 13, '21 at 7:59 pm
hey already found the problem. thank you 
martellsara Apr 14, '21 at 12:25 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login