Get all column data in csv file (from column 100 to column 110, 150)

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

[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

[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

[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

[Squashman]

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.

[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,

[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

[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,

[aGerman]

If this is not a secret, could you copy your updated script into your reply?

Steffen

[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

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

[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();

[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