Getting data from a csv file

[nolivy]

Hi, I have a csv file that has 120 columns and adds a line every 5 minutes (if i open it from excel) and is automatically updated every 5 minutes, is there a way to get specific data (columns) from this csv file into excel file and at the same time the excel file is also updated every 5 minutes.
the filename of my csv is database.csv.

thanks, any help on this matter will be highly appreciated.

[MrShorty]

Data menu -> Import External data -> Import data -> locate file -> follow the steps in the Text import wizard (on step 3, select the columns you want to import) -> when it asks where you want to import the data, click on the "properties" button, where one of the options will be how often you want the data to be refreshed.

Does that do what you want?

[nolivy]

Data menu -> Import External data -> Import data -> locate file -> follow the steps in the Text import wizard (on step 3, select the columns you want to import) -> when it asks where you want to import the data, click on the "properties" button, where one of the options will be how often you want the data to be refreshed.

Does that do what you want?

Thanks it works well now, there is one more thing though, the first column that I import displays in excel as 2.01107E+11, this is supposed to be the date and time, before i used the =text function, now if i use the same function (=text) i need to put it in another cell, my question is there any codes or special function that i need to do so that it will auto update and add a new line for the added lines from the csv?

overall, thanks for the guide..it is a big help.

[nolivy]

To explain further, i get the csv and excel file running. thanks for the guide,works perfectly. What i need to do next is that in A column it is in a date/time format like 2.01107E+11. I like to make it to a YYYY-MM-DD / HH:MM format. What are the options I can do to work around this,and hopefully can do it automatically as the date/time is often updated from a csv file every 5 minutes. Thanks.

[MrShorty]

If that first number is a standard time stamp (1900 or 1904 system), then you should be able to use a number format to get it to show as a date/time.

before i used the =text function, now if i use the same function (=text) i need to put it in another cell, my question is there any codes or special function that i need to do so that it will auto update and add a new line for the added lines from the csv?

If the =text() function worked previously, then it can still work here. Put the formula in the column immediately next to the data import range, then make sure that the "fill down formulas in adjacent columns" option is checked in the "data range properties" dialog. It should then copy that formula down as rows are added.

[nolivy]

Wow,great...works like magic....you are a savior Mrshorty......one question though, if data from the csv file where i extracted like for example the first row were deleted,would the excel file still contain that value? Thanks.

[MrShorty]

I'm pretty sure that it would not. When Excel refreshes the data range, it replaces the existing data with what is in the file on refresh. If something gets deleted from the text file, it will be deleted in Excel the next time the data range is refreshed.

If it is important that data not get lost, you'll need to control that with the program generating the text file, or you'll need to go a different route when importing the data to Excel.

[nolivy]

Thank you for the information sir, it is also what I thought, my problem is the program/device that generates the csv file will delete old data after 30,000 entries. Is there a work-around for that on excel? What I am doing at the moment is copy all the data from the excel file and use paste special-then choose values on a different worksheet, sounds primitive but its working at the moment, the only thing is the file size is double.

[nolivy]

one last thing sir, is there a way to automatically alternate shading in rows, i already used up the conditional formatting to other task. thanks again.

[Kenneth Hobson]

Something like this might work for you. Right click the sheet where the data is imported, View Code and paste. This will add the rows as the cell in column DP changes but not if the row has no data.

Please Login or Register  to view this content.

IF you select a range and Insert > Table, the rows will be shaded alternately. Or select range, Home > Format as Table, and pick a style.

[MrShorty]

my problem is the program/device that generates the csv file will delete old data after 30,000 entries. Is there a work-around for that on excel?

Not a "built in" command. If it has to be automated, then you would need a macro like what Ken has suggested or similar.

How important is it that it be automatic? 30,000 rows at 1 row every 5 minutes = about 100 days worth of data. As much effort as it could take to code a macro that I was sure wouldn't lose data, I'd be tempted to go in every month or two and manually copy and paste like you are doing. It may be "primitive" as you say, but primitive sometimes means "simple" which can sometimes be the best solution.

[nolivy]

Hi again Sir, thanks for the help, I got it all running now, I have another issue though, How can I share this over the network? I got the master file residing on my local disk, i did tried to make another file linked to my master file over the network, the problem is it will not auto-refresh/auto-update once two or more people opened the file at once. Any help please?

Thanks