Import Ascii file with a comma to decimal separator

AmiBroker
#1

Hello Guys,

I'm trying import an ascii file, with a comma to decimal separator (I'm from brazil).

I already read the documentation https://www.amibroker.com/guide/d_ascii.html and not find any reference.

Example of file:
Ticker;Date;Time;Open;Max;Min;Close;Volume;Qtd
VALE3;21/12/2018;17:10:00;50,94;50,98;50,80;50,94;8.711.232,00;171.200
VALE3;21/12/2018;17:00:00;51,07;51,12;50,94;50,97;13.022.520,00;255.100
VALE3;21/12/2018;16:40:00;51,05;51,14;51,00;51,02;18.582.686,00;363.900
VALE3;21/12/2018;16:30:00;50,79;51,07;50,72;51,02;19.875.435,00;390.300
VALE3;21/12/2018;16:20:00;50,51;50,90;50,50;50,77;113.447.936,00;2.238.300

Anyone had deal with same issue, have some clue?
Thanks

#2

Try with the same few lines:
open in text editor.
Replace all , with .
Replace all ; with ,

import into AB.

This is what I get doing the above:
Ticker,Date,Time,Open,Max,Min,Close,Volume,Qtd
VALE3,21/12/2018,17:10:00,50.94,50.98,50.80,50.94,8.711.232.00,171.200
VALE3,21/12/2018,17:00:00,51.07,51.12,50.94,50.97,13.022.520.00,255.100
VALE3,21/12/2018,16:40:00,51.05,51.14,51.00,51.02,18.582.686.00,363.900
VALE3,21/12/2018,16:30:00,50.79,51.07,50.72,51.02,19.875.435.00,390.300
VALE3,21/12/2018,16:20:00,50.51,50.90,50.50,50.77,113.447.936.00,2.238.300

#3

@janderson, to import data correctly you probably need to use a text editor or a script to replace some chars in your file as indicated by @Chris25.
I'm not aware of alternative ways / settings to get it directly, but maybe someone else with more experience knows how to do it and will answer you.

In any case, if you go for the "text replace" solution I think that the proper way to do it is according to the following order:

  1. Replace all the . (dots) with "" (nothing) to transform 8.711.232,00 to 8711232,00 (volume numbers should be without any separator in the integer part, the decimals are set to zero and will not affect the import)
  2. Replace all the , (commas) with dots to transform prices like 60,94 to 60.94 (volume will change to 8711232.00)

Content of the .csv file result after steps 1 and 2:

Ticker;Date;Time;Open;Max;Min;Close;Volume;Qtd
VALE3;21/12/2018;17:10:00;50.94;50.98;50.80;50.94;8711232.00;171200
VALE3;21/12/2018;17:00:00;51.07;51.12;50.94;50.97;13022520.00;255100
VALE3;21/12/2018;16:40:00;51.05;51.14;51.00;51.02;18582686.00;363900
VALE3;21/12/2018;16:30:00;50.79;51.07;50.72;51.02;19875435.00;390300
VALE3;21/12/2018;16:20:00;50.51;50.90;50.50;50.77;113447936.00;2238300

immagine
(I mapped Qtd to OI, but probably it is something else.)

Result:
immagine

2 Likes
#4

Hi @janderson,

It looks like your numerical values are formatted according to European tradition, where the "," (comma) is used as the decimal point, and "." (period) as thousands separator. Leaving the ";" (semicolon) to act as the field delimiter.

I couldn’t find any mention in the AB doc you linked to, relating to “thousands separator”.

If the suggestions from @chris25 and @beppe don't suit/ work for you, could you go back to the source of the data, and specify export in US format, which is what AB is mainly geared-for.

Another alternative, if it’s a once-off import, create a brand new spreadsheet, paste the data, and convert it to columns using the “;” as delimiter in the text-to-columns wizard. This is what I get in Excel 365, before doing a global search/replace on “,”:

Ticker Date Time Open Max Min Close Volume Qtd
VALE3 21/12/2018 17:10:00 50,94 50,98 50,80 50,94 8.711.232,00 171.2
VALE3 21/12/2018 17:00:00 51,07 51,12 50,94 50,97 13.022.520,00 255.1
VALE3 21/12/2018 16:40:00 51,05 51,14 51,00 51,02 18.582.686,00 363.9
VALE3 21/12/2018 16:30:00 50,79 51,07 50,72 51,02 19.875.435,00 390.3
VALE3 21/12/2018 16:20:00 50,51 50,90 50,50 50,77 113.447.936,00 2.238.300

You might also need to look at the default thousands separator for your spreadsheet, and possibly even Windows, which may be having an effect on the formatting of the data you receive. In Excel, “File, Options, Advanced”.

#5

Thanks Chris25,

but I'm triyng do it in a automatically way, because I want import data in a periodically time, like each one hour.

Thanks anyway

#6

Thanks also to @beppe, @phase21,
but is same case, I want do it automatically.
Thanks anyway

#7

You might use AutoIt
https://www.google.com/search?q=autoit

#8

@janderson you simply need to write a script to do the text replace prices files and then invoke the import (or as indicated by @codejunkie look for some tool or command line utility do the same).

You can do the process externally, via some scripting language (JS, VB, Python, etc.) plus OLE to import data in AmiBroker, using Windows Task Scheduler to do it at intervals, but probably it could also be achieved via the Batch functionality directly in AmiBroker.

In the latter case, launch your "replace text" script/utility via "Execute and Wait" or - alternatively - run a formula that will read, change and rewrite the .cvs text files and then do the Ascii import action.
If you opt for AFL code to process your .csv file, you could do something vaguely similar to what was used to replace some lines in this past thread.

#9

For what is is worth AmiBroker ASCII importer automatically handles comma as decimal point IF ONLY field separator is set to something else than default comma.
So you do NOT need to do any replacements. All you need to do is to choose SEMICOLON as separator, i.e. use command

$SEPARATOR ;

image

and data with comma used as decimal point can be imported without any problem

1 Like
#10

@Tomasz, nice to know.

I did a short test, and it seems that in this specific case, your suggestion works well for prices, but volume numbers (that in the @janderson file have dots to separate the thousands) were not properly imported:

immagine

immagine

Maybe he still needs to clean up (or export in a different way) the volume numbers, or there is something else that I missed to import correctly also numbers with thousand separators?

(Test done with AB 6.30.0 - 64-bit)

1 Like
#11

Yes, he may need to remove thousand separators (in his case dots) from volume.

#12

@janderson - a couple of other options, taking into account the suggestions by @codejunkie, @beppe, and @Tomasz.

Option 1
R has the read.csv() function, with he following declaration:

read.csv(file, header = TRUE, sep = ",", quote = """,
dec = ".", fill = TRUE, comment.char = "", ...)

, where dec = can be used to specify the symbol for the decimal place in numeric values, ie.:

dec : the character used in the file for decimal points.

I haven't tested it yet with your data, but it suggests that dec will modify how the incoming data is interpreted before formatting it into R's internal representation.

Assuming it imports correctly, you can then re-export it in almost any structure you want. The beauty of it is, that you can automate the whole process outlined by @beppe.

Only downside, is having to install R and creating a script to do the conversion.

If you use python, or some other scripting language, they'll also have something similar.

Option 2
Use an AFL script to open the text file, and process the string yourself. Then use AB's batch facility.

#13

Instead of installing whole "R" package and using slow R csv importer, it is just a matter of applying StrReplace http://www.amibroker.com/guide/afl/strreplace.html

Easily done from AFL:

ih = fopen("input.txt", "r" );
oh = fopen("output.txt", "w" );
if( ih AND oh ) 
{ 
   while( ! feof( fh ) ) 
   { 
     line = fgets( fh ); // read a line of text
     line = StrReplace( line, ".", "" ); // remove dots
     fputs( line, oh );
   }

   fclose( ih ); 
   fclose( oh );
}
2 Likes