Merge all csv files into one file AND add ticker symbol

[lordoftrades]

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
Espen

 

[lordoftrades]

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...

http://www.makeuseof.com/tag/write-simple-batch-bat-file/

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

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

Kind regards
Espen

 

[Kevin Schmit]

Quote from lordoftrades:

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.

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

 

[lordoftrades]

Quote from Kevin Schmit:

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

Thanks, I will look it up right away.

Kind regards
Espen

 

[lordoftrades]

Quote from Kevin Schmit:

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

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
Espen

 

[tradelink]

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

 

[lordoftrades]

Quote from tradelink:

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

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
Espen

 

[2rosy]

http://pandas.pydata.org/pandas-docs/stable/io.html

 

[lordoftrades]

Quote from 2rosy:

http://pandas.pydata.org/pandas-docs/stable/io.html

Thanks, very useful article!!