Import CSV into workbook tab

[Tanquen]

I have an existing xlsm file with a tab called "CSV".

I'd like to add a button and use VBA to open the CSV file and place the text into the CSV tab and then use the text to columns function.

I'd also like to place the button on a different Tab than the CSV Tab and have it place the CSV file data into the CSV Tab and not the current active tab.

I have been just opening the csv in notepad and then doing a cut and paste into the CSV tab and then running the Excel Text to Columns function. That works but it would be nice to just drag the file in or click a button and open the csv file from the same folder.

It would also be cool if I could get the Text to Columns to stay active. Like any time you paste text in column A it just uses the same settings as before.

 

[gelu]

You can do all that with Power Query (PQ) (no copy paste, notepad).
Once you do it once it automates all subsequent imports.


The first step depends a bit on Excel version.
The thing is to use the Import from .csv option from somewhere in Data menu.

Excel 2016 and above has pretty much the menus as below.
For Excel 2010-2013 you need to install Power Query add-inn, free from Msft. It becomes menu tab itself.


Step 1

Step 2 - Select path\File_xxy.csv. This is going to be the Source for PQ, which means PQ will remember exactly the path and the name of the file.

If you always save the csv files in the same folder there could be a different approach (use the folder as the Source):

How To Import All Files In A Folder With Power Query | How To Excel

www.howtoexcel.org

 

[Tanquen]

Wow, thanks for the detailed info but it seems like a lot of work or steps. Isn't what I'm doing now, faster? I'm not sure how this would work with my current setup. You did say once you set it up once you should be good to go but only if the file and path to the CSV and its name never changes?

Currently the main Excel file is copied and then the accompanying CSV is added to the CSV sheet or tab. The names of both files change though they are normally located in the same folder. That's why I was thinking of a button that I could click on the main sheet in the workbook and have it default to the current Excel files folder and just click on or pick the CSV file.

The workbook has some other VBA scripts that manipulate the CSV tab and once finished I then have another VBA script that grabs a few of the key columns from the CSV tab and creates a new CSV file. That part works pretty well, it's set up to create the new CSV files name based on object names in the original CSV and give it a date and place it in the same location as the current main Excel file.

 

[gelu]

Hi T,

All those steps only once if source fixed. You can change the source in a few clicks if addresses change. Let me know if yo are interested and I'll write it down.
One way or another you need to import the csv in Excel. Power Query (an ETL tool) is better than the legacy text import wizard.

Success!

 

[Tanquen]

Hi T,

All those steps only once if source fixed. You can change the source in a few clicks if addresses change. Let me know if yo are interested and I'll write it down.
One way or another you need to import the csv in Excel. Power Query (an ETL tool) is better than the legacy text import wizard.

Success!

I'll give it a shot. I just thought it would be easier to have VBA automate what I do manually. Just open a file and cut and paste the text into a TAB and then run the text to columns function.

 

[Akbarov]

Change ranges to your need.

Assign it to button.
It will open browser for you to choose CSV file you want to import.
And insert it to DATA sheet.

Sub ImportData()

    Dim xFileName As Variant
    Dim rg As Range
    Dim xAddress As String
    xFileName = Application.GetOpenFilename(, , , False, False)
    If xFileName = False Then Exit Sub
    On Error Resume Next
    Set rg = ThisWorkbook.Worksheets("DATA").Range("A24:A2000")
    On Error GoTo 0
    If rg Is Nothing Then Exit Sub
    xAddress = rg.Address
    With Sheets("DATA")
        With .QueryTables.Add("TEXT;" & xFileName, .Range(xAddress))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = False
        .Refresh BackgroundQuery = False
        End With
    End With
End Sub

 

[Tanquen]

Thanks! That seems to work.

It is removing a character from the first "; " line but I think that is all it's changing.

; Created on: 10/12/2022
Created on: 10/12/2022