1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

CSV Files

Discussion in 'Business Applications' started by imtiaz107e6, Apr 22, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. imtiaz107e6

    imtiaz107e6 Thread Starter

    Joined:
    Apr 22, 2010
    Messages:
    3
    Hi!

    I don't know whether this is the correct forum, but hope somebody can help.

    I have about 220 separate CSV files of different companies stock historical data as follows:

    date,open,high,low,close,volume

    I download the daily End Of Day data in CSV which has the following:

    Company Name,date,open,high,low,close,volume

    Is there a way the daily company quotes can be updated automatically to each individual company's file?

    I have no programming experience.

    Any help will be appreciated.

    Tks in advance.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What program are you using to collate the data?
     
  3. imtiaz107e6

    imtiaz107e6 Thread Starter

    Joined:
    Apr 22, 2010
    Messages:
    3
    Right now I use a software called winmerge where I open the EOD data in one pane and open the individual company file, one at a time in another pane, then copy and paste the info.

    rgds
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Sorry I don't have any experience with winmerge. I am surprised that you are not Excel or Access to do this.
    I would suggest that you create a new Thread with winmerge in the title to attract anyone with knowledge of it.
    Or if possible add it to the Title of this thread.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi,
    I use the code I have added to the attached file to read my csv files.
    The code I found on different sites and have editted some parts myself.
    You will still have to add some code of your own to specify the file type, separator to read it, but the import part will read any file for you.
    Maybe you can out it to use, alternative if it doesn't work attach one or two of the csv files you have so that I can do the coding for you. :)
    I use Excel 2003 but it will work under 2007 too. (y)
     

    Attached Files:

  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Keebellah, they don't use Excel!
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I know, but Winmerge is nothing more than a text comparisson tool.
    So that's why I sent them the Execl sheet.
    I could use the same code in a vbs script but then waht do they need, want?
    Where does the collated data have to be stored, saved, shown?
    Let's wait and see
     
  8. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    It is possible to make a code that will open the EOD file, and to add the data to the specific company file, but you would need to have one more file that will specify the correspondence between the Name of the company in EOD file and the name of the file that contains this company's data.
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Maybe the filename contains the company naem, so you could use that as additional data?
     
  10. imtiaz107e6

    imtiaz107e6 Thread Starter

    Joined:
    Apr 22, 2010
    Messages:
    3
    Hi!

    Tks for the reply. I do use excel but sorry you have to explain what I have to do with the file u sent.

    I will clarify further. I use winmerge as I dont have an alternative. It helps me to look at the Master file in one pane and then I can open the other files one at a time in the other pane copy from the master and paste in the other files. I use the individual company named files with a charting software which requires files to be in CSV format.

    I am attaching the Master file which contains the EOD data and some of the individual files of companies so that it will make it clear.

    Only the date,open,high,low,close,volume data from the master has to be exported to the company's individual file.

    Tks and Rgds.
     

    Attached Files:

  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you are using Excel, why don't you do it all in an Excel workbook, including the sorting & Charting?
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Here is a solution using a ms-dos mbatch file and a vbs script.
    Run the batch file to start the thing:

    The batch file AND the vbs script must be in the same foder (together) where the csv files to be processed are

    Run the batch file and you'll have the masteryyyymmdd.csv file.
    Notice that I turned around your date notation for sorting reasons

    The bacth file will always delete an existing master*.csv file before starting.
    It lists all csv files to a file and the vbs script is triggered.
    This one reads the csvlist.lst and processes all the files one by one adding the name and symbol field as per your example.
    Let me know if it does the job for you
     

    Attached Files:

  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Just a small addtion.
    The output file is now numbered Masterjjjjmmdd-000.csv
    this is just in case the nr of records exceed 65000
    Excel up to version 2003 only allows 65536 rows.
    So now if the total nr of lines exceed 65000 the new mastwr output is creade and it starts again.

    The result is shown in a dialog and the master files will be 001, 002 etc.
    Default is 001 for less than 65000 records

    I foyu don't want this, use the previous one and disregard
     

    Attached Files:

  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Maybe I'm doing too much but since I work with these type of files I want to make sure it works all the time.

    I have attached the zip file again. It contains an EOD folder with a batch file and a vbs script file
    CSVMaster.bat
    CSV2Master.vbs

    The bat file you can use to run the vbs script.
    To work correctly it needs to knwo where to read the csv files, and where to store the output.
    The first time you run it it will promt to craete a INI file, well I think it explaings itself.
    You will have to Select the Sourcefolder and for the Outpur you can either select or create a folder.
    The INI file and the List file that will contain the csv files will always be stored in the same folder where the batch and vbs are found.
    In this folder a _logs folder will be created and there a log sile will be or created or appenden with some info.

    when you test it the vbs script will at the end show a dialog with the results.
    If you edit the vbs script (Notepad is Okay) you can set ShowResults = False and then schedule the batch file to run at the end of the day.
    The log file will show what is has doen.

    The only think I havent doen is to move the read csv files to another location so that you won't be reading the same files over and over again.

    You could add a line to the batch file to do this.
    Keep in mind that it has to do this only in the script has run correctly, so before the first goto end statement in the batch file.

    I added the Master20100429-001.csv which has been created with the script.

    Hope I could explain my train of thought.;)
     

    Attached Files:

  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/918516

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice