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
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How to import .csv file to excel correctly using VBA?
0
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)
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: 1989) 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: 1989) Apr 14, '21 at 9:45 am
yesss, i used
i just used Local:=true
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
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 SubActually, 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.
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.
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