CSV Search AutoIT


CSV Search AutoIT



I have a CSV file that contains 4 columns, I want to search column 2 and change the corresponding data in column 4 using AutoIT:


col 1 col 2 col 3 col 4
1 502 shop 25.00
2 106 house 50.00
3 307 boat 15.00




2 Answers
2



If the columns are separated by tabs then you could use StringSplit for that.


$s1 = '1 502 shop 25.00'
$s2 = '2 106 house 50.00'
$s3 = '3 307 boat 15.00'

For $i=1 To 3
$array = StringSplit(Eval('s' & $i), @TAB)
ConsoleWrite('Column 2: "' & StringStripWS($array[2], 8) & '"' & @CRLF)
ConsoleWrite('Column 4: "' & StringStripWS($array[4], 8) & '"' & @CRLF)
Next



This sample code will print:


Column 2: "502"
Column 4: "25.00"
Column 2: "106"
Column 4: "50.00"
Column 2: "307"
Column 4: "15.00"



EDIT



This example creates a CSV file, then reads the file back in and searches every line for '106'. If the string is found and the last column has the value of '50.00', then this value is replaced with '22.00'. The result is written to a new CSV file.


; write the data to the CSV file
Global $hFile = FileOpen('test.csv', 10)
If $hFile = -1 Then Exit
FileWrite($hFile, '1' & @TAB & '502 ' & @TAB & 'shop' & @TAB & '25.00' & @CRLF & _
'2' & @TAB & '106 ' & @TAB & 'house' & @TAB & '50.00' & @CRLF & _
'3' & @TAB & '307' & @TAB & 'boat' & @TAB & '15.00')

FileClose($hFile)

; read the CSV file and create a new one
If Not FileExists('test.csv') Then Exit
Global $hFileIn = FileOpen('test.csv')
Global $hFileOut = FileOpen('test_new.csv', 10)

While 1
Global $sLine = FileReadLine($hFileIn)
If @error = -1 Then ExitLoop

If StringInStr($sLine, '106') Then
$sLine = _ReplacePrices($sLine)
ConsoleWrite('New price: ' & $sLine & @CRLF)
EndIf
FileWriteLine($hFileOut, $sLine)
WEnd
FileClose($hFileIn)
FileClose($hFileOut)
Exit

; search for "106" find that and the corresponding value in
; column 4 (50.00) and change the column 4 value to "22.00"
Func _ReplacePrices($sLineFromCSVFile)
Local $array = StringSplit($sLineFromCSVFile, @TAB)

If StringStripWS($array[2], 8) = '106' And _
StringStripWS($array[4], 8) = '50.00' Then
Return $array[1] & @TAB & $array[2] & @TAB & _
$array[3] & @TAB & '22.00'
EndIf
EndFunc



If you run the example this will be the result:



enter image description here



search column 2 and change the corresponding data in column 4



Example using _ArraySearch() :


_ArraySearch()


#include <FileConstants.au3>
#include <File.au3>
#include <Array.au3>

Global Enum $CSV_COL1, _
$CSV_COL2, _
$CSV_COL3, _
$CSV_COL4

Global Const $g_sFileInp = @ScriptDir & 'input.csv'
Global Const $g_sFileOut = @ScriptDir & 'output.csv'
Global Const $g_sFileDelim = @TAB
Global Const $g_iColSearch = $CSV_COL2
Global Const $g_iColRepl = $CSV_COL4
Global Const $g_sValSearch = '502'
Global Const $g_sValRepl = '35'

Global $g_iRow = 0
Global $g_aCSV

_FileReadToArray($g_sFileInp, $g_aCSV, $FRTA_NOCOUNT, $g_sFileDelim)

While True

$g_iRow = _ArraySearch($g_aCSV, $g_sValSearch, ($g_iRow ? $g_iRow + 1 : $g_iRow), 0, 0, 0, 1, $g_iColSearch, False)
If @error Then ExitLoop

$g_aCSV[$g_iRow][$g_iColRepl] = $g_sValRepl

WEnd

_FileWriteFromArray($g_sFileOut, $g_aCSV, $CSV_COL1, Default, $g_sFileDelim)
ShellExecute($g_sFileOut)






By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

-

Popular posts from this blog

api-platform.com Unable to generate an IRI for the item of type

api-platform.com Unable to generate an IRI for the item of type I'm trying to setup my first api-platform instance and have run into some presumably noob questions around IDE. What i'm trying to do is setting up an register and reset entity in order to deal with registrations and password reminders from an app. And then have the entity's passed to a custom controller to do some checks, balances and mails afterwards (haven’t gotten around to that yet). But I keep running into this error when posting to my custom entities: { "@context": "/api/contexts/Error", "@type": "hydra:Error", "hydra:title": "An error occurred", "hydra:description": "Unable to generate an IRI for the item of type "AppXXXUserBundleEntityRegister"", "trace": [ { "namespace": "", "short_class": "", "c
Read more

How to set up datasource with Spring for HikariCP?

How to set up datasource with Spring for HikariCP? Hi I'm trying to use HikariCP with Spring for connection pool. I'm using jdbcTempLate and JdbcdaoSupport. This is my spring configuration file for datasource: <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"> <property name="dataSourceClassName" value="oracle.jdbc.driver.OracleDriver"/> <property name="dataSource.url" value="jdbc:oracle:thin:@localhost:1521:XE"/> <property name="dataSource.user" value="username"/> <property name="dataSource.password" value="password"/> </bean> But unfortunately the following error message is generating: Cannot resolve reference to bean 'dataSource' while setting bean property 'dataSource'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource'
Read more

Display dokan vendor name on Woocommerce single product pages

Display dokan vendor name on Woocommerce single product pages With woocommerce I am using Dokan plugin and I am trying to display the vendor name, rating and vendor location on single product pages. I tried this code to display vendor name but no luck: //show store name add_action( 'woocommerce_single_product_summary', 'show_store_name', 20 ); function show_store_name() { printf( '<b>Seller Name:</b> <a href="%s">%s</a>', dokan_get_store_url( $author->ID ), $store_info['store_name'] ); } Any help is appreciated. 1 Answer 1 I don't use dokan plugin. Here is the way to get the (post) author ID and to make your code work: add_action( 'woocommerce_single_product_summary', 'display_author_name', 20 ); function display_author_name() { // Get the author ID (the vendor ID) $vendor_id = get_post_field( &#
Read more