Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
OldFella

Obtaining Data from CSV Files

Question

Hi Caleeco

Nice to see a little bit of new blood on the site, to help keep the grey matter up to scratch for designing eVTOL flying taxis or whatever else they keep you busy with during the day.

 

I have a new problem. I'm trying to import data into an open worksheet, on a daily basis, from a series of unopened .csv files.

 

If the .csv file is opened with Excel, each file may have hundreds of rows but only two columns - a date in col A and a numerical value in col B. The date is currently displayed in yyyymmdd format although of course it's text, not a date. I'm planning to change the display so that it reads ddmmyyyy which, to me, will look a bit more normal, even though without separators.

 

On the open worksheet my thinking was to enter the date of interest, then use VLOOKUP to find that date in the .csv files of interest and return the numerical value in col B. That works fine if the .csv file is open, but not so good when it's closed.

 

So - I have two queries:
1.  Since VLOOKUP won't work with a closed file, I need VBA to achieve what I'm after as my understanding is that VBA can access closed files. As I'm now totally out of my depth, could you possibly generate appropriate coding for me?
The .csv files are in F:\Trading\ASX\Portfolio. I won't run through the whole list, I'll be able to add to or modify your coding once I see what you've done, but the first few are AJM.csv, CTM.csv, CTMOB.csv and CVS.csv.
The destination workbook is Portfolio.xlsm and is in the F:\Trading folder. The destination for the numeric value from the AJM.csv file is cell F7; from CTM.csv is F8; from CTMOB.csv is F9; and so on. Let me know if you need further detail.
2.  A date that displays as ddmmyyyy looks horrible without separators. How do I convert that text into either dd.mm.yyyy or dd/mm/yyyyy? And having done that, can the date in that format be used to match up with the date in the .csv files? If they won't match, then maybe I'll need to use a hidden column on the destination worksheet and do it in reverse - convert dd.mm.yyyy (which will display on the worksheet) into ddmmyyyy (which will be in a hidden column). Does that make sense.

 

Many thanks
Ye Olde one

(and I won't mention that game that was played in Russia recently)

Share this post


Link to post
Share on other sites

9 answers to this question

Recommended Posts

  • 0
7 hours ago, OldFella said:

for designing eVTOL flying taxis

 

:D haha! I can neither confirm or deny my involvement in such things! 

 

Thanks for the new problem! You can access closed .xlsx files, but this tends to be very slow - I've never tried it on CSV files but dont imagine it's possible. I have some thoughts as to how to go about this....there are several options:

Query 1

  1. Open each CSV file in a set folder (will keep opening until there are none left), extract the data you need, close the CSV, move onto the next
  2. Setup a data connection to each CSV, so they are imported upon workbook open. Then VLOOKUP should work as you expect on the imported data

It would be useful to see the format of the data in the CSV files, is it consistent? are the columns delimited by a special character? are you able to email me a sample file?

 

Query 2

You can use the Format Cells command:

  • Highlight Cells > CTRL + 1
  • On the 'Number' Tab, Choose 'Date' and pick a format you like

However, I presume this is imported data, that excel sees as TEXT instead of a DATE. In which case you need a formula to convert it. 

2r39fzn.jpg

 

So Cell C2 will dispay the 'True' date and can be manipulated as expected. The formula for reference is below:

=LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4)

 

Haha, not to worry. I only watch the World Cup for the entertainment.. not that invested into the sport as a whole!

 

Kind Regards

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco

My sincere apologies for the prolonged delay following up with this. Far, far too many side issues!

 

I've made some slight changes to the requirements, which hopefully have simplified it a bit. Anyway, here's the description:
a)  In a folder named 'Results' I have a worksheet named 'Summary';
b)  Column B contains a list of symbols, usually three alpha characters but the length can be between 1 and 5. Data starts in row 6 and currently extends down to row 23 although the last data row will change from week to week;
c)  In the 'Results' folder there is a sub-folder named 'Data1' in which there are (currently) 18 .csv files. These files have the same name as each symbol in col B of the 'Summary' sheet. Cell B6 of 'Summary' has the symbol AAD; in the 'Data1'  folder there is a .csv file named AAD.csv. Cell B7 of 'Summary' has the symbol BBD; in the 'Data1' folder there is a .csv file named BBD.csv. And so on . . . .
d)  If I open them with Excel, each .csv file has just 4 cells of data. A1 and B1 are headers, A2 contains a date and B2 contains a numeric value. The .csv files are comma delimited and are created, on demand, using an external process. A typical file (in text mode) is:
"Date","Close"
"24/09/2018","0.67"
The quote marks are optional and are not really needed.
That was the easy part.

 

I need VBA coding that I can attach to an "UPDATE" button and that will:
a)  Read the value in 'Summary' cell B6;
b)  Open the .csv file (in read only mode) that has the title of the value in B6;
c)  Read the value in the .csv file cell B2;
d)  Close the .csv file;
e)  Write the value to 'Summary' cell F6;
f)  Read the value in 'Summary' cell B7;
g)  Open the .csv file (in read only mode) that has the title of the value in B7;
h)  Read the value in the .csv file cell B2;
i)  Close the .csv file;
j)  Write the value to 'Summary' cell F7;
k)  Read the value in 'Summary' cell B8;
l)  Open the .csv file (in read only mode) that has the title of the value in B8;
and continue looping until it reaches a blank cell in 'Summary' col B, each value in 'Summary' col B has been read and the value in cell B2 of the corresponding .csv file has been read and then written to 'Summary' col F.

 

I've found that I'm unable to achieve the required result using formulae because regardless of how I try, I cannot get the data in 'Summary' to automatically update whenever I generate each new batch of .csv files unless I open each individual .csv file. Hence the need for VBA coding to open and close each .csv file.

 

Hope all that makes sense. I've done away with the need for VLOOKUP, and the date problems have been resolved, so it's down to just needing looping coding that will sequentially copy a single cell of data to the summary sheet.

 

Many thanks

Share this post


Link to post
Share on other sites
  • 0

Hi again Caleeco

 

Sorry to be a pain but I've just been simulating the effect of the requested coding and I've found a small issue.

 

There are times when not every symbol in 'Summary' col B will have a corresponding .csv file. I can readily overcome this by increasing the amount of data in each .csv file, but it complicates the coding slightly because instead of reading data from a specific cell in the .csv file it will now need to read the last value in the column. So, in items c) & h) above, please replace "Read the value in the .csv file cell B2"  with "Read the last value in col B of the .csv file". That should ensure there is always a .csv file to correspond with each symbol in 'Summary' col B and hence each symbol would have a value in col F, although for safety it might be sensible to include some error trapping to allow for a situation where there is no corresponding .csv file.

 

And of course the first line in d) of the upper block is no longer valid; there will now be multiple cells of data and different numbers of cells in each .csv file. In text mode, it's always the last value in the last line that has to be read.

 

Thanks

  • Like 1

Share this post


Link to post
Share on other sites
  • 0
On 7/18/2018 at 6:08 AM, Caleeco said:

You can access closed .xlsx files, but this tends to be very slow - I've never tried it on CSV files but dont imagine it's possible. 

Hi Caleeco

I've just come across a web page that shows that accessing a closed .csv file is practicable with VBA. Have a look at https://blog.udemy.com/vba-open-file/ and scroll down to Example 2. I'm sure that will make far more sense to you than it does to me - hope it helps.

Or open it as a workbook?

😉

Share this post


Link to post
Share on other sites
  • 0

Hey OldFella!

 

Thanks for getting back to me. Sounds like an interesting problem to solve!

 

Are you able to share any of your source files? This will enable much easier testing (when importing data, the structure of both source and output data is important to understand properly). If not, don't worry I'll create a dummy setup and let you test.

 

Thanks for the udemy link! I'll give it a read 

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco

You should already have both an early iteration of the worksheet and a sample .csv file, both emailed to you on 18 July.

 

The worksheet is very much a work-in-progress and the current layout looks somewhat unlike the version emailed to you, but the concept is identical. I noted in the first post on Monday that the value that's read from the .csv file has to be written to 'Summary' column F; in the sample worksheet I emailed to you it's to be written to column G.

 

Let me know if you don't have those files and I'll resend them. I recall we had confusion months ago with me sending things to the wrong WTF address; maybe I did it again!  😩

Share this post


Link to post
Share on other sites
  • 0
3 minutes ago, OldFella said:

Hi Caleeco

You should already have both an early iteration of the worksheet and a sample .csv file, both emailed to you on 18 July.

 

The worksheet is very much a work-in-progress and the current layout looks somewhat unlike the version emailed to you, but the concept is identical. I noted in the first post on Monday that the value that's read from the .csv file has to be written to 'Summary' column F; in the sample worksheet I emailed to you it's to be written to column G.

 

Let me know if you don't have those files and I'll resend them. I recall we had confusion months ago with me sending things to the wrong WTF address; maybe I did it again!  😩

 

Hey OldFella,

 

I just had a scan of my email account... I couldn't see any files from you in July. Could you please re-send and I'll start coding tomorrow! (It's 1am here). 

 

[email protected]

 

Look forward to hearing from you :)

Caleeco 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...