Page 1 of 1
Get all column data in csv file (from column 100 to column 110, 150)
Posted: 14 Apr 2021 02:42
by Comet297
Hi guys,
I have csv file with 1001 columns
I want to get all data of column 100 to column 110 and column 150 into another csv file.
How can I do it?
Thank you for your reading and I hope get your support.
Best regards,
Comet297.
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 14 Apr 2021 10:22
by aGerman
I'd probably use the aid of JScript in a Batch-hybrid.
*.bat
Code: Select all
@if (0)==(0) echo off &setlocal
set "infile=test.csv"
set "outfile=out.csv"
set "separator=,"
cscript //nologo //e:jscript "%~fs0" "%infile%" "%outfile%" "%separator%"
goto :eof @end
var oFSO = new ActiveXObject('Scripting.FileSystemObject'),
oInFile = oFSO.OpenTextFile(WScript.Arguments(0)),
oOutFile = oFSO.OpenTextFile(WScript.Arguments(1), 2, true),
delim = WScript.Arguments(2),
line = '',
arr = [],
idx = 0;
while (!oInFile.AtEndOfStream) {
line = oInFile.ReadLine();
arr = line.split(delim);
for (idx = 99; idx < 110; ++idx) { oOutFile.Write(arr[idx] + delim); }
oOutFile.WriteLine(arr[149]);
}
oInFile.Close();
oOutFile.Close();
Update at least the first 2 variables to meet your file names. Also, depending on your local settings the data separator in your files could be a semicolon rather than a comma.
Steffen
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 14 Apr 2021 20:05
by Comet297
Hi Steffen,
Thank you so much for your answer to solve my stuck
But this code doesn't return result.
1. I opened csv by notepad -> separator return comma (,)
2. I put test.csv and out.csv in the same folder *.bat file, run your code
3. I try put test.csv, out.csv into C:\ and changed (set "infile=C:\test.csv", set "outfile=C:\out.csv")
Have any wrong of my operation?
Best regards,
Comet
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 15 Apr 2021 00:09
by aGerman
Rather don't put the file directly into the C:\ root because you might have restricted access to create the output file. Besides of that it should work though. At least it worked for me.
Insert a PAUSE between the CSCRIPT and GOTO lines to keep the window open. Maybe you get an error message that helps to find the reason ...
Steffen
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 15 Apr 2021 01:05
by Squashman
Comet297 wrote: ↑14 Apr 2021 20:05
3. I try put test.csv, out.csv into C:\ and changed (set "infile=C:\test.csv", set "outfile=C:\out.csv")
Have any wrong of my operation?
Best regards,
Comet
That capability has not existed without elevated privileges' since Windows Vista. So roughly 15 years.
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 15 Apr 2021 19:19
by Comet297
Hi,
Firstly, I'd like to say thank you for your support so much!
I tested this code on other PC, it returned result as I want.
Problem is java version. Old PC java version: 1.6.0, New PC java version 1.8.0
Thank you and best regards,
Comet,
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 16 Apr 2021 00:29
by aGerman
I can ensure you that the Java version has nothing to do with it. The script uses JScript (which is the Windows-specific implementation of ECMAScript commonly known as JavaScript).
Steffen
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 16 Apr 2021 08:59
by Comet297
Hi Steffen,
You are right. After posted answer, I tested on 5 machines. Only 1 machine can run this code. Another can not. I upgraded Java same version but all error return same: Microsoft JScript compilation error: Expected ";"
I search solution for this matter but until now not yet find.
Do you have any suggguest for me?
Comet,
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 16 Apr 2021 09:29
by aGerman
If this is not a secret, could you copy your updated script into your reply?
Steffen
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 19 Apr 2021 09:50
by Comet297
Hi Steffen,
I'd like to say thank you again,
I attached file into this post. I can run this code by some PC but some PC at my company can not run. I guest it was block by IT rule.
Best regards,
Comet
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 19 Apr 2021 11:22
by Squashman
As was already stated, this has nothing to do with JAVA, so the screenshot of that is irrelevant.
Also, please always post code, input and output as text in your responses instead of screen shots.
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 19 Apr 2021 12:57
by aGerman
I downloaded your CSV file with two different browsers in order to verify that the lines actually end with a single carriage return (0D if you observe the file content in a HEX editor). If this is the same in your real CSV files, it will probably never work.
Can you confirm that this is true? On Windows lines do usually end with carriage return and line feed (0D 0A). I'm pretty sure the ReadLine() method can also handle lines that end with line feed only (0A). However, carriage return only is just too weird. I'm wondering what application generated such kind of output
Steffen
// EDIT Give this code a go:
Code: Select all
@if (0)==(0) echo off &setlocal
set "infile=test.csv"
set "outfile=out.csv"
set "separator=,"
cscript //nologo //e:jscript "%~fs0" "%infile%" "%outfile%" "%separator%"
pause
goto :eof @end
var oADOStrm = new ActiveXObject('ADODB.Stream'),
oFSO = new ActiveXObject('Scripting.FileSystemObject'),
oOutFile = oFSO.OpenTextFile(WScript.Arguments(1), 2, true),
delim = WScript.Arguments(2),
line = '',
arr = [],
idx = 0;
oADOStrm.Type = 2;
oADOStrm.LineSeparator = 0x0D;
oADOStrm.Charset = "x-ansi"
oADOStrm.Open();
oADOStrm.LoadFromFile(WScript.Arguments(0));
while (!oADOStrm.EOS) {
line = oADOStrm.ReadText(-2);
arr = line.split(delim);
if (arr.length > 149) {
for (idx = 99; idx < 110; ++idx) { oOutFile.Write(arr[idx] + delim); }
oOutFile.WriteLine(arr[149]);
}
}
oADOStrm.Close();
oOutFile.Close();
Re: Get all column data in csv file (from column 100 to column 110, 150)
Posted: 20 Apr 2021 02:47
by Comet297
Hi Steffen,
I want to say thank you more and more.
Base on your comment, I found my mistake.
1. Line feed (0D 0A) because I posted csv(Macintosh) which save as by excel, I tried all csv format so I post wrong.
2. All codes can run by my PC, which PC I copied your code directly and made *bat file. But my company PC can not send in/out easily. I used company chat app to transfer text -> it automatical change ' to ` => it make code wrong. When I correct it, everything become OK.
Thank you so much, you waste many times for my question and keep calm for my mistake.
Best regard,
Comet