FORUMS BROKERS SOFTWARE
Home
 
    Forums > Technically Speaking > Programming > Merge all csv files into one file AND add ticker symbol


Reply
 
Thread Tools
Old Aug 22nd, 2012, 05:15 PM   #1
lordoftrades
Registered User
 
Join Date: Jan 2011
Location: Norway
Posts: 45
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
    Quote
Old Aug 22nd, 2012, 06:46 PM   #2
lordoftrades
Registered User
 
Join Date: Jan 2011
Location: Norway
Posts: 45
I think I got a workaround... Using a batch code:
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-s...atch-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
    Quote
Old Aug 22nd, 2012, 11:12 PM   #3
Kevin Schmit
 
 
Join Date: Nov 2005
Location: New York
Posts: 597
Quote:
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
    Quote
Old Aug 23rd, 2012, 03:21 AM   #4
lordoftrades
Registered User
 
Join Date: Jan 2011
Location: Norway
Posts: 45
Quote:
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
    Quote
Old Aug 23rd, 2012, 05:22 AM   #5
lordoftrades
Registered User
 
Join Date: Jan 2011
Location: Norway
Posts: 45
Quote:
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
    Quote
Old Aug 23rd, 2012, 12:02 PM   #6
tradelink
 
 
Join Date: Feb 2008
Posts: 577
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
    Quote
 
Reply
Thread Tools

Forum Jump



   Conduct Rules   Privacy Policy   Sitemap Copyright © 2014, Elite Trader. All rights reserved.   

WHILE YOU'RE HERE, TAKE A MINUTE TO VISIT SOME OF OUR SPONSORS:
Advantage Futures
Futures Trading & Clearing
AMP Global Clearing
Futures and FX Trading
Collective2
Automated Trading Services
CTS
Futures Trading Software
dom993trading.com
NinjaTrader Consulting
eSignal
Trading Software Provider
FXCM
Forex Trading Services
Global Futures
Futures, Options & FX Trading
Interactive Brokers
Pro Gateway to World Markets
JC Trading Group
Direct Access Trading
MB Trading
Direct Access Trading
NinjaTrader
Trading Software Provider
optionshouse
Option Trading & Education
Rithmic
Futures Trade Execution Platform
SpeedTrader
Direct Access Trading
SpreadProfessor
Spread Trading Instruction
thenut-trader.com
FX, Gold, & Stock Signals
TradersStudio
System Building & Backtesting
Tradier
Equity and Options Trading
Trading Technologies
Trading Software Provider