Multiple data import from text files

[Nils_Junker]

Hi everybody,

I got the following problem:

I need to get data from round about 500 text files.
Problem one is that I just need a part of each text file, (the part I need always stays the same)
Problem two is that I dont have enough time to manually convert all the text files one by one.

The following link shows all the files I want to include in my excel

Index of /climate_environment/CDC/observations_germany/climate/10_minutes/air_temperature/recent/

Also how many lines can i insert in Excel?

If you need further Information, please send me an info!

Thanks for your help!

 

[Nils_Junker]

So has this file downloaded and extracted?

but i want that this textfile is directly converted in excel

or how would it normally go on?

 

[Herakles]

but i want that this textfile is directly converted in excel

or how would it normally go on?

So it appears that it was something to with the computer drive that you were using.

Lets move on but know what was causing the problem may be useful in the future.

So now try selecting 10 files for example to see what happens.

So now that it works we can look at what you want to do with these text files,

As you can see, some of them are rather large.

My suggestion is that each text file is imported one at a time and a filter applied to only keep the lines that you need
and put these lines in a single worksheet.

Remind me what the filter is to be.

It still may be that the lines that you want to keep exceed the rows in a worksheet but one step at a time.

 

[Nils_Junker]

So it appears that it was something to with the computer drive that you were using.

Lets move on but know what was causing the problem may be useful in the future.

So now try selecting 10 files for example to see what happens.

So now that it works we can look at what you want to do with these text files,

As you can see, some of them are rather large.

My suggestion is that each text file is imported one at a time and a filter applied to only keep the lines that you need
and put these lines in a single worksheet.

Remind me what the filter is to be.

It still may be that the lines that you want to keep exceed the rows in a worksheet but one step at a time.

I already tried with more files and it works.

the filter does not have to be applied, i already worked out a formula which works.
i need to download about 10 files at a time so i think it would be enough if the text files just automatically go in the other sheet.

each file has 79201 lines times 10 that would fit into excel

 

[Nils_Junker]

So it appears that it was something to with the computer drive that you were using.

Lets move on but know what was causing the problem may be useful in the future.

So now try selecting 10 files for example to see what happens.

So now that it works we can look at what you want to do with these text files,

As you can see, some of them are rather large.

My suggestion is that each text file is imported one at a time and a filter applied to only keep the lines that you need
and put these lines in a single worksheet.

Remind me what the filter is to be.

It still may be that the lines that you want to keep exceed the rows in a worksheet but one step at a time.

so to sum it up, now the only thing which is missing is that the text files will automatically insert in this excel.
is it possible that it automatically is formatted right, so that each information gets a own column?
the disconnector is a ";" i think

 

[Herakles]

so to sum it up, now the only thing which is missing is that the text files will automatically insert in this excel.
is it possible that it automatically is formatted right, so that each information gets a own column?
the disconnector is a ";" i think

Importing just 10 files at a time will take ages and it can be automated.

What is this formula that you have in mind?

 

[Nils_Junker]

Importing just 10 files at a time will take ages and it can be automated.

What is this formula that you have in mind?

it's a FILTER Formula, i already tried it where i manually downloaded a .zip file and used it.
how long does it take round abourt for 10 files?

but if this is easy i only would need the lines from one month.
so it would be from 202305010010 to 202305312350
this information is in a textfile right behind the ID from the station

 

[Herakles]

it's a FILTER Formula, i already tried it where i manually downloaded a .zip file and used it.
how long does it take round abourt for 10 files?

but if this is easy i only would need the lines from one month.
so it would be from 202305010010 to 202305312350
this information is in a textfile right behind the ID from the station

I've written some code to import the files.

It's taking a while so I'll let it run.

I'll then test if there is a faster way.

 

[Nils_Junker]

I've written some code to import the files.

It's taking a while so I'll let it run.

I'll then test if there is a faster way.

okay can you send me the code or will you first look if there is a faster way?

and by the way, thanks man i honestly appreciate your help so much
you are the best

 

[Herakles]

There is so much data.

Did you say that you only need certain columns.

If I could only import some it would help.

 

[Nils_Junker]

There is so much data.

Did you say that you only need certain columns.

If I could only import some it would help.

Yes i only need certain columns. In the attached Screenshot you can see which columns i need from the textfile:

So i need the "STATIONS_ID", "MESS_DATUM" and "TT_10"

and also as you can see at the "MESS_DATUM" i only need the data from 202305010010 up to 202305312350 (Month May)