Importing data from CSV/TEXT file

Hello members,

I am trying to set up a vba code with no success. Is it possible to import CSV file with macro ? I want to go on date tab > choose From Text/CSV > Import the file and click Load automatically.

Thank you for providing the vba code. It doesnt work for some reason. When I add this vba code and try to run it, I am getting error on "Dim textFile As Textstrem" - Compile error:User-defined type not defined.

this message here ' must reference Microsoft Scripting RunTime - you need to add this reference to your project in the VBA Editor

I am sorry, but where exactly to add it ? The steps I did, Take the vba code and place it my module, than change the Sub to my name and after I run it, I got this message above.

Tools > Reference > scroll down to Microsoft Scripting Runtime > click the checkbox > click ok

https://bettersolutions.com/vba/visu...references.htm

Thank you very much. Its working now, but my file is in CSV format... sorry I forgot to send the csv, is it possible to add csv ?

You can simply change the delimiter on this line

Please Login or Register  to view this content.

from the ; to ,
The text file posted in the first post has ;

Thank you this solves the issue with text and csv. But now when I load the original csv, I face another big issue.. . Sorry to be that annoying
When I do it manually Data>from CSV/TEXT and import it, it does stay organized in columns. For example
Colum A/row 1 - Subject
Colum B/ row 1 -Start date and so one.

When I do it with the macro it upload it differently.
Please check photo with name TEST 4

Here is the phoho

If you look at the workbook I posted you can see why it is writing to where it is.

You can change that with this line writeToRow = 4, you'll want to change this line as well Sheet1.Cells(2, 4).Value = " with " & (writeToRow - 5) & " records. " or introduce a new variable to count the number of lines read from the text file.

The code just directly reads the file and posts each row to the sheet. There is no known formatting of the sheet that you're importing the file into.

edit: typo

I changed to line writeToRow =50 and started from row 50, but still it's not in consistent order.
It should be for example:
Row 50 Subject, Start date, End Date and so on.. btw it has on each row " for some reason .
Row 51 X,Y,Z, etc

At the moment the text its everywhere. I guess its something with rows I need to do but can not figure it out.

I'd have to see the actual file you're trying to import; it seems much different than the example file you uploaded.

EDIT: you can DM me

Hi,
Here is the file

This file is nowhere close to the example file. It would take quite a while to create a process to map the data to the cells you want from this file.
Given the large number of lines without comma separated data (should it be ignored?)

As stated, the code supplied is for your example you supplied.
If you want the lines w/o actual data, use an if statement that checks for comma and if there is one then parse the line and write it to the sheet.

Thank you very much for spending your time. Well I guess, that I will just give up on that since its kind of hard for me.
Anyway thanks a lot.

Originally Posted by daneca

... Here is the file

dobro utro daneca ... please provide the appearance (in an xlsx file) of an example result of importing data from a csv (12,csv) file.

Hi mjr veverka,

This is how it looks when you go the steps, Data>From TEXT/CSV > Load and it do a table. My idea is to insert the file with a macro button and then create a pivot table.

Why do you need a macro if you use PQ ?
It's like trying to transport a couch in a passenger car when you have a pickup truck in the garage ...

Hi,

Because I have to make several adjustments and formation after the table is uploaded. I have a separate macro for it.
My goal was to have Sheet 1 with two buttons. One to import the csv file and the second to do the adjustment, but I guess its not possible

Originally Posted by daneca

... but I guess its not possible ...

It is possible, but the issue is how stable/constant the structure of the csv file attached as an example is ... e.g.:

Please Login or Register  to view this content.