Batch to edit .CSV

February 18, 2010 at 00:28:06
Specs: Windows XP
Hi everyone,

I have a problem with the automaticly created .CSV files.

The .CSV files are made by OmniPage Pro 17 software. I want to import the .CSV file in Access to a database, but the .CSV doesnt have colomnames so every word comes verticaly in table.

I want to make a batch file who can add the colomnames in the .CSV file so it doesnt comes vertical in table.

Omnipage creats text_0001.csv text_0002.csv text_0003.csv ect.

Thank you in advance.


See More: Batch to edit .CSV

Report •


#1
February 18, 2010 at 04:37:46
@echo off > newfile & setLocal enableDELAYedeXpansion

set colomnames=name age weoght

for /f "tokens=* delims= " %%a in ('dir/b/a-d text_????.csv') do (
> newfile echo.!colomnames!
type %%a >> newfile
move /y newfile %%a
)


=====================================
Helping others achieve escape felicity

M2


Report •

#2
February 18, 2010 at 06:04:39
Thank you for the reaction Mechanix2Go's.

I try it and it did not work. So i change things to try but it didnt work.

One example of the .csv file. maybe it makes it more clear.

", Jacobus"
"MA"
"2"
"dec. 1883"
"Ma 1:"
"2:"
"overl."
"Strijp,"
"Geb"
"van AD"
"1850-60";""
"1860-70";""
"1870-80";""
"1880-90";""
"1890-00";""
"1900-15";""
"1915-39";"1, 3-1"
"DW";""
"Inst"
"000021"


I hope you can help me out. :)


Report •

#3
February 18, 2010 at 06:56:31
I ran it on a file with the contents you specified and I got this: [First line is new header.]

=======================
name age weight city
", Jacobus"
"MA"
"2"
"dec. 1883"
"Ma 1:"
"2:"
"overl."
"Strijp,"
"Geb"
"van AD"
"1850-60";""
"1860-70";""
"1870-80";""
"1880-90";""
"1890-00";""
"1900-15";""
"1915-39";"1, 3-1"
"DW";""
"Inst"
"000021"


=====================================
Helping others achieve escape felicity

M2


Report •

Related Solutions

#4
February 18, 2010 at 07:08:58
Oke?

I just copy and past it and call it test.cmd.

I also made csv files called text_0001.csv and text_0002.csv.

It doesnt change anything to text_0001.csv or a new document called newfile.

I am using Windows XP service pack 3. Can you tell me what I am doing worng, because i just copy and paste it.


Report •

#5
February 18, 2010 at 10:10:41
Post the script you are using.


=====================================
Helping others achieve escape felicity

M2


Report •

#6
February 19, 2010 at 00:11:56

@echo off > newfile & setLocal enableDELAYedeXpansion

set colomnames=name age weoght

for /f "tokens=* delims= " %%a in ('dir/b/a-d text_????.csv') do (
> newfile echo.!colomnames!
type %%a >> newfile
move /y newfile %%a
)

This is the code. Btw do you know if there is a way to put this ; between "" "".
Like "hi";"HI";

And agian Thank you


Report •

#7
February 19, 2010 at 00:35:23
The script looks OK.

Next questions:

Where is the script?

Where are the CSV files?


=====================================
Helping others achieve escape felicity

M2


Report •

#8
February 21, 2010 at 23:54:25
Hi Mechanix2Go,

I haven't answer the qeustion, because i was on holiday.
The script and the Csv file are in the same map. I try also to put the csv file on a supmap as the script, but that didnt work also.


Report •

#9
February 22, 2010 at 02:18:17
I'm not quite with map and submap.

Try this: make a new local directory with a simple name. Like C:|TEST

Put the script and 2 CSV files in it and test. Run it from the CMD prompt; not by clicking.

What did you call your script?


=====================================
Helping others achieve escape felicity

M2


Report •

#10
February 22, 2010 at 04:04:19
Well it works, i put them in C:test

Script: cmd.cmd
CSV: text_0001
CSV: text_0002

The next qeustion is. I need to put everything in two lines.
The first line is what we input: set colomnames=name age weoght

The second line is the whole csv file. This is the orgnial: ", Jacobus"
"MA"
"2"
"dec. 1883"
"Ma 1:"
"2:"
"overl."
"Strijp,"
"Geb"
"van AD"
"1850-60";""
"1860-70";""
"1870-80";""
"1880-90";""
"1890-00";""
"1900-15";""
"1915-39";"1, 3-1"
"DW";""
"Inst"
"000021"

And i want it like this after running the batch script.

"name";"age";"weight";"city";
", Jacobus";"MA";"2";"dec. 1883";"Ma 1:";"2:";"overl.";"Strijp,";"Geb";"van AD";"1850-60";"";"1860-70";"";"1870-80";"";"1880-90";"";"1890-00";"";"1900-15";"";"1915-39";"1, 3-1";"DW";"";"Inst";"000021";

So everything one two lines with ; between "" "", is this possible?


Report •

#11
February 22, 2010 at 06:31:21
OK One thing at a time. I made a typo: c:]test should be c:\test, but I think we're past that.

Next case: Are you telling me your script is named cmd.cmd?

A script name shound never use a reserved word or an existing command.

Let's rename it CSVFIXER.BAT ; note the extension.

Moving right along, you want the whole original CSV on line 2, right?


=====================================
Helping others achieve escape felicity

M2


Report •

#12
February 22, 2010 at 07:13:58
Yes i want the whole CSV on line 2, but it must be separated by ; between "" "".

like: "Ma";"2".

And i renamed it to CSVFIXER.BAT.

Thanks for your reaction


Report •

#13
February 22, 2010 at 12:36:12
@echo off > newfile & setLocal enableDELAYedeXpansion

set colomnames=name age weight city

for /f "tokens=* delims= " %%a in ('dir/b/a-d text_????.csv') do (
set S=
for /f "tokens=* delims= " %%i in (%%a) do (
set S=!S!%%i
)
> newfile echo.!colomnames!
>> newfile echo.!S!
move /y newfile %%a
)


=====================================
Helping others achieve escape felicity

M2


Report •

#14
February 23, 2010 at 04:47:27
Hi there :+)

Thanks for your reaction agian Mechanix2Go.

I try the code, but there is one thing. I try it but i can find it.
because its a automaticly created .CSV file there are some problems with it.
This happens when i run it.
name age weight city
"Johan";"25";"""New York";


This is how it should be to import on the right way in Access:
name age weight city
"Johan";"25";"";"New york"";.

Because there was no info about the weight it is empty. So I need to find a way to set the ; on the right place. Because if i import it now, it doesnt look good.

Do you have some ideas for me?


Report •

#15
February 24, 2010 at 01:54:55
[1] The files are not, strictly speaking, CSV.

[2] Since you want to use the data in Access you may be better off with an Access app or macro to get the data into the required shape.

Excel may also be helpful in this.

There is an Office forum on this site where I bet there are some pretty smart Access users.

Good luck.


=====================================
Helping others achieve escape felicity

M2


Report •

#16
February 24, 2010 at 02:55:13
Thank you for helping me out so far M2.

Report •


Ask Question