Merge all csv files into one file AND add ticker symbol

Discussion in 'Programming' started by lordoftrades, Aug 22, 2012.

  1. Hi

    I have found a simple code that will merge all my csv files into one file. But the problem is that QCollector doesn't have symbol in the first column. Therefore the merged file is unusable as it is all one big collection of qoutes without knowing what stock they are representing.

    Here's the code that merge the files, and it works fine except from the missing symbol:

    Start>Run>type "cmd"

    Then make sure all your csv files are in the same folder. I caled my folder help and put it in the C: folder.

    Type CD C:help (hit enter)

    You should see some verification that you are in this folder.

    Type copy *.csv name.csv

    Note there is a space between the word copy and the * and a space between.csv and name. Also note that you can change name to anything you want. This will copy all csv files an combine them in a file called name.csv. It copies them in such a manner that it is identical to copying and pasting multiple csvs in excell on the vertical axis 1 to infinity (Not a to vv). It's pretty sweet.

    If anyone know how to do this I would really appreciate it.

    I would like to create a SQL Database for intraday-data, and EOD-data.

    Kind regards
  2. I think I got a workaround... Using a batch code:
    @echo off
    setLocal EnableDelayedExpansion
    for /f "tokens=* delims= " %%c in ('dir/b/a-d *.csv') do (
      set FN=%%~Nc
      set /a N=0
      for /f "tokens=* delims= " %%a in (%%c) do (
        set /a N+=1
        if !N! equ 1 (
          echo %%a, id > !FN!.csv
        ) else (
          echo %%a, !FN! >> !FN!.csv
    And I did follow the instructions on how to create a .bat file, which I never tried before...

    Then I will try to merge the csv files tomorrow....

    If anyone have better solutions I'm happy to hear about it.

    Kind regards
  3. Install cygwin.

    Then run:

    for FILE in *.csv; do sed -e 's/^/'$FILE',/' $FILE > a.csv; done<rtn>

    This will prefix each line in the combined file with the
    original file name, which identifies the symbol

  4. Thanks, I will look it up right away.

    Kind regards
  5. Hah, this was not as easy as I thought it might be.. I'm like a green vegtable when it comes to these command-boxes.

    I have installed cygiwn, and i opened the commandbox.

    But from there I don't know what to do.

    I have a folder/string on my desktop called Intraday/Textfiles/IQ/Intraday, inside this folder all the files are stored that I want to merge and add the filename to.

    If you don't mind I would be really happy to get a step-by-step guidance on what to type to get the correct folder.

    Kind regards
  6. slightly easier to use sed from gnu32win since you can use from normal command line.

    open command line
    type 'start .', this will open a folder to current directly
    copy your csv files into this folder
    then run the command kevin gives above
  7. Hi tradelink

    I get message "FILE was not excpected now" when I run that line from kevin.

    Is this codeline made for a merged file to lookup its matching values and then pull the names? Or is it going to merge and pull filename all in one process?

    It's the 'all-in-one' option that I tried now.

    Kind regards
  8. 2rosy