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

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
Comet297
Posts: 6
Joined: 14 Apr 2021 02:30

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

#1 Post by Comet297 » 14 Apr 2021 02:42

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
Expert
Posts: 4180
Joined: 22 Jan 2010 18:01
Location: Germany

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

#2 Post by aGerman » 14 Apr 2021 10:22

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
Posts: 6
Joined: 14 Apr 2021 02:30

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

#3 Post by Comet297 » 14 Apr 2021 20:05

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
Expert
Posts: 4180
Joined: 22 Jan 2010 18:01
Location: Germany

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

#4 Post by aGerman » 15 Apr 2021 00:09

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
Expert
Posts: 4292
Joined: 23 Dec 2011 13:59

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

#5 Post by Squashman » 15 Apr 2021 01:05

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.

Comet297
Posts: 6
Joined: 14 Apr 2021 02:30

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

#6 Post by Comet297 » 15 Apr 2021 19:19

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
Expert
Posts: 4180
Joined: 22 Jan 2010 18:01
Location: Germany

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

#7 Post by aGerman » 16 Apr 2021 00:29

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
Posts: 6
Joined: 14 Apr 2021 02:30

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

#8 Post by Comet297 » 16 Apr 2021 08:59

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
Expert
Posts: 4180
Joined: 22 Jan 2010 18:01
Location: Germany

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

#9 Post by aGerman » 16 Apr 2021 09:29

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

Steffen

Comet297
Posts: 6
Joined: 14 Apr 2021 02:30

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

#10 Post by Comet297 » 19 Apr 2021 09:50

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
Attachments
Test.csv
(159.67 KiB) Downloaded 36 times
JScript.JPG
JScript.JPG (60.06 KiB) Viewed 1449 times
Java version.JPG
Java version.JPG (17.76 KiB) Viewed 1449 times

Squashman
Expert
Posts: 4292
Joined: 23 Dec 2011 13:59

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

#11 Post by Squashman » 19 Apr 2021 11:22

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
Expert
Posts: 4180
Joined: 22 Jan 2010 18:01
Location: Germany

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

#12 Post by aGerman » 19 Apr 2021 12:57

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
Posts: 6
Joined: 14 Apr 2021 02:30

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

#13 Post by Comet297 » 20 Apr 2021 02:47

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

Post Reply