1

I want to ask if how can I achieve importing CSV file to my Excel file matching the columns of my template using macro (or VBA).

Here's why I'm frustrated to ask this question. I searched for answer. Google didn't help. No one is asking for this type of question.

I have a file name "UserInfo.xlsx" which has a column headers:

  • UserID
  • Username
  • Name
  • ContactNumber
  • EmailAddress
  • LastLogin

Now I have a CSV file (generated by our website) with some excess headers which I don't need.

The headers from my CSV file and headers from my template are exactly the same. The problem is I want to get those information based on their column headers (CSV) that will match the template's header.

How can I achieve this? I will use this template everyday because new users are signing up to our website. So I need a VBA code for this.

I don't know where to start so I'm asking for your help.

Thank.

Improve this question

3 Answers 3

Reset to default
2

You can query the csv file with an ADODB.Connection. If you have over 65K record then CopyFromRecordset probably won't work and you'll have to use ADODB.Recordset.GetRows to retrieve the data.

Sub Example()
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Dim conn As Object, rs As Object
Dim Filename As String, FilePath As String

FilePath = "C:\"
Filename = "UserInfo.csv"

'Refernce: https://msdn.microsoft.com/en-us/library/ms974559.aspx
'Excel VBA Introduction Part 30 - ADO (ActiveX Data Objects) Querying a Database: https://www.youtube.com/watch?v=HE9CIbetNnI&index=33&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & FilePath & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""

rs.Open "SELECT [UserID],[Username],[Name],[ContactNumber],[EmailAddress],[LastLogin] FROM [" & Filename & "]", _
        conn, adOpenStatic, adLockOptimistic, adCmdText

Worksheets("Sheet1").Range("A1").CopyFromRecordset rs

rs.Close
conn.Close
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Can I ask how to use ADODB.Recordset.GetRows?
I created an example workbook: Download Example Workbook
1

Your code works like a charm! Although I change it a little bit since I'm using Windows 10 and I think Microsoft.Jet.OLEDB.4.0 doesn't work with my current Excel version (2016).

I changed Microsoft.Jet.OLEDB.4.0 to Microsoft.ACE.OLEDB.12.0

I also found out that the FilePath variable is wrong. I removed the filename which is UserInfo.csv and I was shocked when I tried running it. Works as expected.

Since I don't have enough points to post a screenshot, I can say that your code really and I mean really works!

Thank you so much.

Here's my final code:

Sub Example()
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Dim conn As Object, rs As Object
Dim Filename As String, FilePath As String

FilePath = "C:\" 'Just remove the filename and extension
Filename = "UserInfo.csv"

'Refernce: https://msdn.microsoft.com/en-us/library/ms974559.aspx
'Excel VBA Introduction Part 30 - ADO (ActiveX Data Objects) Querying a Database: https://www.youtube.com/watch?v=HE9CIbetNnI&index=33&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5

Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & FilePath & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited""" 'Change the Provider

rs.Open "SELECT [UserID],[Username],[Name],[ContactNumber],[EmailAddress],[LastLogin] FROM [" & Filename & "]", _
        conn, adOpenStatic, adLockOptimistic, adCmdText

Worksheets("Sheet1").Range("A1").CopyFromRecordset rs

rs.Close
conn.Close
End Sub

Again thank you so much Thomas Inzina. You have just saved my life. :)

Improve this answer

4 Comments

I have a question Thomas. Is it possible to import my CSV files in UTF-8 encoding?
The excepted answer to this post shows you how: Is it possible to force Excel recognize UTF-8 CSV files automatically? You could record a macro to find out the steps. Can you provide me a sample csv file?
Another question. I know this is kinda off-topic but how can I stop Excel converting a data into numbers. Example, the usercode is 0008, Excel automatically converts it into 8 literally. Or should I ask how to stop excel removing the preceding zeros and treat it as a normal string?
You can format the cells as text. If all the ID's have 4 characters you can use a custom NumberFormat format e.g. if [A1].NumberFormat = "0000" then if [A1] = 8 then [A1].Value would equal 8 and [A1].Text would = 0008.
0

Contextures Excel Sample Data

I would record a macro importing your file using these steps: How to import a .csv file that uses UTF-8 character encoding. You should be able to easily customize the recorded macro to fit your needs.

Note: When importing CSV files Excel allows you to skip columns.

enter image description here

Improve this answer

5 Comments

Thanks for this. I already found an answer and it works fine.
I add CharacterSet=65001 to my connection string. conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & FilePath & ";" & _ "Extended Properties='text;HDR=YES;FMT=Delimited(,);CharacterSet=65001'"
Another question (sorry for too much questions hehehe) Is it possible to scan first the file before putting it to the designated sheet? I mean it will first read the column row by row. If detected a number with preceeding zeros or any number, automatically it will add semicolon in front of that number. Is it possible? I wonder how to do that.
A CSV file is just a text file. There are several ways to read and write to it. But if you add delimiters you will through off the columns.
Oopss. Hahaha sorry. Maybe because I often use OpenOffice to open CSV files. Hahahaha.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.