Copy different texts from excel into different files

Get help with using AutoHotkey and its commands and hotkeys
NoviceUser
Posts: 7
Joined: 13 Apr 2015, 20:25

Copy different texts from excel into different files

13 Apr 2015, 20:55

Hello everyone, I am a brand new user, done some very basic .bat file type writing in my past but nothing like what I am liking to complete now. I have tried using another program called TextCrawler to do this but thinking i will need a combination on that and AHK to get the results i am needing. Basically I am trying to make a cookie cutter to making 160 files with the same overall layout but different points from a database in each file. I have 1 page I have created from scratch, got the sign off that is what everyone likes, and now they want all the pages to look exactly the same for all the other data. 1 page took about 4 hours to make, don't want to repeat that process 160 more times. I wrote a .bat file to copy my original 159 time from a list of predefined file names and that worked great. so now i have basically 160 files with different names but same content in each. Now i need to replace 16 different words in each file from the original to its own values. Excel sort and filter did a good job of getting that but find/replace in notepad each time is super time consuming. TextCrawler has a batch editor i can do all 16 find replaces on each file but i need to currently copy the 16 values to a tab separated text file, import it into text crawler, select the file i want to run the replace on, execute, clear the batch out, copy the next files worth of data, save, import, select file, run, rinse repeat and even that is super time consuming. Any help would help, I am very very new at this and have read a ton of posts which both answered questions and raised even more. Thank you in advance for any help, Below I have copied and pasted a few things to see what the spreadsheet looks like, file names etc...I am not stuck with textcrawler, if there is another way to do this without the piece of software i am all ears just wanted to share all the information i have.

Spreadsheet for 1 page worth of find/replace tab separated:
M139_D2_10A_A UnitImpedance001 M139_D2_10A_A UnitImpedance005
M139_D2_10A_A UnitImpedance002 M139_D2_10A_A UnitImpedance006
M139_D2_10A_A UnitImpedance003 M139_D2_10A_A UnitImpedance007
M139_D2_10A_A UnitImpedance004 M139_D2_10A_A UnitImpedance008
INSTANCE="640" INSTANCE="644"
INSTANCE="641" INSTANCE="645"
INSTANCE="642" INSTANCE="646"
INSTANCE="643" INSTANCE="647"
M139_D2_10A_A UnitVoltage001 M139_D2_10A_A UnitVoltage005
M139_D2_10A_A UnitVoltage002 M139_D2_10A_A UnitVoltage006
M139_D2_10A_A UnitVoltage003 M139_D2_10A_A UnitVoltage007
M139_D2_10A_A UnitVoltage004 M139_D2_10A_A UnitVoltage008
INSTANCE="520" INSTANCE="524"
INSTANCE="521" INSTANCE="525"
INSTANCE="522" INSTANCE="526"
INSTANCE="523" INSTANCE="527"

File Names of the first 4 files. Above would cover the first file name:
Battery Data M139_D2_10A_A05_A08.aspx
Battery Data M139_D2_10A_A09_A012.aspx
Battery Data M139_D2_10A_A013_A016.aspx
Battery Data M139_D2_10A_A017_A020.aspx
Battery Data M139_D2_10A_A021_A024.aspx
User avatar
Blackholyman
Posts: 1292
Joined: 29 Sep 2013, 22:57
Facebook: socialjsz
Google: +Jszapp
Location: Denmark
Contact:

Re: Copy different texts from excel into different files

14 Apr 2015, 05:11

I'd like to help but may still need a little more info from you...
M139_D2_10A_A UnitImpedance001 M139_D2_10A_A UnitImpedance005
So in this line 001 needs to be replaced with 005 and its the same for all 16 lines, left of tab replaced with right of tab,

How do you get this info (what do you do to get it), whats the format before you reformat it? what does a done file's content look like.

whats your hope with this
Also check out:
Courses on AutoHotkey

My Autohotkey Blog
:dance:
Heezea
Posts: 53
Joined: 30 Sep 2013, 21:33

Re: Copy different texts from excel into different files

14 Apr 2015, 12:39

Have you considered using the file name in a formula in excel?

Cell formula = 2+2&"test"
Gives a value of 4test.

To get the filename see this article:
https://support.office.com/en-us/articl ... #BMinsert3

Can we assume the notepad files are being generated by the revised excel sheets?
FanaticGuru
Posts: 1472
Joined: 30 Sep 2013, 22:25

Re: Copy different texts from excel into different files

14 Apr 2015, 14:48

NoviceUser wrote: Spreadsheet for 1 page worth of find/replace tab separated:
M139_D2_10A_A UnitImpedance001 M139_D2_10A_A UnitImpedance005
M139_D2_10A_A UnitImpedance002 M139_D2_10A_A UnitImpedance006
M139_D2_10A_A UnitImpedance003 M139_D2_10A_A UnitImpedance007
M139_D2_10A_A UnitImpedance004 M139_D2_10A_A UnitImpedance008
INSTANCE="640" INSTANCE="644"
INSTANCE="641" INSTANCE="645"
INSTANCE="642" INSTANCE="646"
INSTANCE="643" INSTANCE="647"
M139_D2_10A_A UnitVoltage001 M139_D2_10A_A UnitVoltage005
M139_D2_10A_A UnitVoltage002 M139_D2_10A_A UnitVoltage006
M139_D2_10A_A UnitVoltage003 M139_D2_10A_A UnitVoltage007
M139_D2_10A_A UnitVoltage004 M139_D2_10A_A UnitVoltage008
INSTANCE="520" INSTANCE="524"
INSTANCE="521" INSTANCE="525"
INSTANCE="522" INSTANCE="526"
INSTANCE="523" INSTANCE="527"

File Names of the first 4 files. Above would cover the first file name:
Battery Data M139_D2_10A_A05_A08.aspx
Battery Data M139_D2_10A_A09_A012.aspx
Battery Data M139_D2_10A_A013_A016.aspx
Battery Data M139_D2_10A_A017_A020.aspx
Battery Data M139_D2_10A_A021_A024.aspx
Do you even need to extract information from a database or is the information predictable based on the file name:

Like:
Battery Data M139_D2_10A_A05_A08.aspx contains:
M139_D2_10A_A UnitImpedance005
M139_D2_10A_A UnitImpedance006
M139_D2_10A_A UnitImpedance007
M139_D2_10A_A UnitImpedance008
INSTANCE="644"
INSTANCE="645"
INSTANCE="646"
INSTANCE="647"
M139_D2_10A_A UnitVoltage005
M139_D2_10A_A UnitVoltage006
M139_D2_10A_A UnitVoltage007
M139_D2_10A_A UnitVoltage008
INSTANCE="524"
INSTANCE="525"
INSTANCE="526"
INSTANCE="527"

Then:
Battery Data M139_D2_10A_A09_A012.aspx contains:
M139_D2_10A_A UnitImpedance009
M139_D2_10A_A UnitImpedance010
M139_D2_10A_A UnitImpedance011
M139_D2_10A_A UnitImpedance012
INSTANCE="648"
INSTANCE="649"
INSTANCE="650"
INSTANCE="651"
M139_D2_10A_A UnitVoltage009
M139_D2_10A_A UnitVoltage010
M139_D2_10A_A UnitVoltage011
M139_D2_10A_A UnitVoltage012
INSTANCE="528"
INSTANCE="529"
INSTANCE="530"
INSTANCE="531"

If it is all just predictable number changes based on numbers in the file name then all the files can be created completely by AHK with no other programs, scripts, Excel, etc. It is fairly easy to have AHK create the 160 files completely.

The files can be created with a bunch of non-changing text around this changing information.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
User avatar
atnbueno
Posts: 85
Joined: 12 Oct 2013, 04:45

Re: Copy different texts from excel into different files

14 Apr 2015, 16:06

Hello NoviceUser.

If I understand your task, you definitely don't need TextCrawler and you could have skipped the .bat that made all the copies. AutoHotkey is ideal to work with text and text files.

Correct me if I'm wrong but you want to read the file Battery Data M139_D2_10A_A05_A08.aspx and make those 16 replacements. Is the last filename Battery Data M139_D2_10A_A0637_A0640.aspx and these its replacements?

Code: Select all

M139_D2_10A_A UnitImpedance001	M139_D2_10A_A UnitImpedance637
M139_D2_10A_A UnitImpedance002	M139_D2_10A_A UnitImpedance638
M139_D2_10A_A UnitImpedance003	M139_D2_10A_A UnitImpedance639
M139_D2_10A_A UnitImpedance004	M139_D2_10A_A UnitImpedance640
INSTANCE="640"	INSTANCE="1272"
INSTANCE="641"	INSTANCE="1273"
INSTANCE="642"	INSTANCE="1274"
INSTANCE="643"	INSTANCE="1275"
M139_D2_10A_A UnitVoltage001	M139_D2_10A_A UnitVoltage637
M139_D2_10A_A UnitVoltage002	M139_D2_10A_A UnitVoltage638
M139_D2_10A_A UnitVoltage003	M139_D2_10A_A UnitVoltage639
M139_D2_10A_A UnitVoltage004	M139_D2_10A_A UnitVoltage640
INSTANCE="520"	INSTANCE="1152"
INSTANCE="521"	INSTANCE="1153"
INSTANCE="522"	INSTANCE="1154"
INSTANCE="523"	INSTANCE="1155"
If so, you can do everything with a loop

Code: Select all

Loop 160
{
	If (A_Index > 1)
	{
		To := A_Index * 4
		From := To - 3
		PaddedTo := To
		If PaddedTo < 100
			PaddedTo := "0" PaddedTo
		If PaddedTo < 10
			PaddedTo := "0" PaddedTo
		Instance1To := To + 636
		Instance2To := To + 516
		; The line below is really long!
		MsgBox, You want to read the file "Battery Data M139_D2_10A_A0%From%_A0%To%.aspx" and replace:`n`n"M139_D2_10A_A UnitImpedance001" with "M139_D2_10A_A UnitImpedance%PaddedTo%"`n...`n'INSTANCE="640"' with 'INSTANCE="%Instance1To%"'`n...`n"M139_D2_10A_A UnitVoltage001" with "M139_D2_10A_A UnitVoltage%PaddedTo%"`n...`n'INSTANCE="520"' with 'INSTANCE="%Instance2To%"'`n...
	}
}

Esc::
	ExitApp
Return
(this code only shows messages with MsgBox but it shows how to assemble the strings you want to use. You can read each file with FileRead, replace strings with StringReplace and save the new file with FileDelete and FileAppend)

Don't despair :) It would have been probably less work generating all the files with AutoHotkey from the start.


Regards,
Antonio

P.S. After playing with your data some time :think: I suspect you would probably have enough with one single file :wtf: with a parameter and using it like this: Battery Data M139_D2_10A.aspx?index=5 :eh: (.aspx scripts can also do whatever you want with text)
NoviceUser
Posts: 7
Joined: 13 Apr 2015, 20:25

Re: Copy different texts from excel into different files

14 Apr 2015, 16:49

You are a very active community and that is awesome! I hope I can return the help in the future with more practice. I will try to answer everyones questions best i can. First i will try to fill in all the blanks. In this case most instance numbers did line up quite well but they are generated as created by a database so might not always be the case. File names do get changed somewhat I will copy whole list as i cannot upload a txt document to the bottom of this post.

Here is what the original database looks like i exported into excel to filter out items and only get the info i needed. This is 3 points worth. Red is the info i needed to replace

<Table1>
<SiteID>1</SiteID>
<Device_ID>2108</Device_ID>
<ObjectType>0</ObjectType>
<ObjectName>M139_D2_10A_A UnitImpedance001</ObjectName>
<Instance>640</Instance>
<ObjectID>66078</ObjectID>
<VirtualParentObjectID>65397</VirtualParentObjectID>
<IsUserCreated>1</IsUserCreated>
<CustomObjectName>M139_D2_10A_A UnitImpedance001</CustomObjectName>
</Table1>
<Table1>
<SiteID>1</SiteID>
<Device_ID>2108</Device_ID>
<ObjectType>0</ObjectType>
<ObjectName>M139_D2_10A_A UnitImpedance002</ObjectName>
<Instance>641</Instance>
<ObjectID>66079</ObjectID>
<VirtualParentObjectID>65397</VirtualParentObjectID>
<IsUserCreated>1</IsUserCreated>
<CustomObjectName>M139_D2_10A_A UnitImpedance002</CustomObjectName>
</Table1>
<Table1>
<SiteID>1</SiteID>
<Device_ID>2108</Device_ID>
<ObjectType>0</ObjectType>
<ObjectName>M139_D2_10A_A UnitImpedance003</ObjectName>
<Instance>642</Instance>
<ObjectID>66080</ObjectID>
<VirtualParentObjectID>65397</VirtualParentObjectID>
<IsUserCreated>1</IsUserCreated>
<CustomObjectName>M139_D2_10A_A UnitImpedance003</CustomObjectName>
</Table1>

This is 1 page from excel that would cover 2 of the points needed to be replaced. Just went down a few cells didnt want to make this post longer than needed.
Cells start at A1 B1 C1 and D1 and do gown from there. there are 2 other tabs just like this one with impedance and cell state with theirr associated instance numbers. Basically A1 and C1 are to be replaced with B1 and D1 respectively. I just basically made a copy of A1-A4 and C1-C4 all the way to the bottom so every 4 rows it would goto the next time on the list. you can see 1,2,3,4 get replaced with 5,6,7,8...then 1,2,3,4 gets replaced with 9,10,11,12 up to 24 then it goes to 10A_B


M139_D2_10A_A UnitVoltage001 M139_D2_10A_A UnitVoltage005 INSTANCE="520" INSTANCE="524"
M139_D2_10A_A UnitVoltage002 M139_D2_10A_A UnitVoltage006 INSTANCE="521" INSTANCE="525"
M139_D2_10A_A UnitVoltage003 M139_D2_10A_A UnitVoltage007 INSTANCE="522" INSTANCE="526"
M139_D2_10A_A UnitVoltage004 M139_D2_10A_A UnitVoltage008 INSTANCE="523" INSTANCE="527"
M139_D2_10A_A UnitVoltage001 M139_D2_10A_A UnitVoltage009 INSTANCE="520" INSTANCE="528"
M139_D2_10A_A UnitVoltage002 M139_D2_10A_A UnitVoltage010 INSTANCE="521" INSTANCE="529"
M139_D2_10A_A UnitVoltage003 M139_D2_10A_A UnitVoltage011 INSTANCE="522" INSTANCE="530"
M139_D2_10A_A UnitVoltage004 M139_D2_10A_A UnitVoltage012 INSTANCE="523" INSTANCE="531"
M139_D2_10A_A UnitVoltage001 M139_D2_10A_A UnitVoltage013 INSTANCE="520" INSTANCE="532"
M139_D2_10A_A UnitVoltage002 M139_D2_10A_A UnitVoltage014 INSTANCE="521" INSTANCE="533"
M139_D2_10A_A UnitVoltage003 M139_D2_10A_A UnitVoltage015 INSTANCE="522" INSTANCE="534"
M139_D2_10A_A UnitVoltage004 M139_D2_10A_A UnitVoltage016 INSTANCE="523" INSTANCE="535"
M139_D2_10A_A UnitVoltage001 M139_D2_10A_A UnitVoltage017 INSTANCE="520" INSTANCE="536"
M139_D2_10A_A UnitVoltage002 M139_D2_10A_A UnitVoltage018 INSTANCE="521" INSTANCE="537"
M139_D2_10A_A UnitVoltage003 M139_D2_10A_A UnitVoltage019 INSTANCE="522" INSTANCE="538"
M139_D2_10A_A UnitVoltage004 M139_D2_10A_A UnitVoltage020 INSTANCE="523" INSTANCE="539"
M139_D2_10A_A UnitVoltage001 M139_D2_10A_A UnitVoltage021 INSTANCE="520" INSTANCE="540"
M139_D2_10A_A UnitVoltage002 M139_D2_10A_A UnitVoltage022 INSTANCE="521" INSTANCE="541"
M139_D2_10A_A UnitVoltage003 M139_D2_10A_A UnitVoltage023 INSTANCE="522" INSTANCE="542"
M139_D2_10A_A UnitVoltage004 M139_D2_10A_A UnitVoltage024 INSTANCE="523" INSTANCE="543"
M139_D2_10A_A UnitVoltage001 M139_D2_10A_B UnitVoltage001 INSTANCE="520" INSTANCE="544"
M139_D2_10A_A UnitVoltage002 M139_D2_10A_B UnitVoltage002 INSTANCE="521" INSTANCE="545"
M139_D2_10A_A UnitVoltage003 M139_D2_10A_B UnitVoltage003 INSTANCE="522" INSTANCE="546"
M139_D2_10A_A UnitVoltage004 M139_D2_10A_B UnitVoltage004 INSTANCE="523" INSTANCE="547"
M139_D2_10A_A UnitVoltage001 M139_D2_10A_B UnitVoltage005 INSTANCE="520" INSTANCE="548"
M139_D2_10A_A UnitVoltage002 M139_D2_10A_B UnitVoltage006 INSTANCE="521" INSTANCE="549"
M139_D2_10A_A UnitVoltage003 M139_D2_10A_B UnitVoltage007 INSTANCE="522" INSTANCE="550"
M139_D2_10A_A UnitVoltage004 M139_D2_10A_B UnitVoltage008 INSTANCE="523" INSTANCE="551"
M139_D2_10A_A UnitVoltage001 M139_D2_10A_B UnitVoltage009 INSTANCE="520" INSTANCE="552"
M139_D2_10A_A UnitVoltage002 M139_D2_10A_B UnitVoltage010 INSTANCE="521" INSTANCE="553"
M139_D2_10A_A UnitVoltage003 M139_D2_10A_B UnitVoltage011 INSTANCE="522" INSTANCE="554"
M139_D2_10A_A UnitVoltage004 M139_D2_10A_B UnitVoltage012 INSTANCE="523" INSTANCE="555"

File Names:

Battery Data M139_D2_10A_A05_A08.aspx
Battery Data M139_D2_10A_A09_A012.aspx
Battery Data M139_D2_10A_A013_A016.aspx
Battery Data M139_D2_10A_A017_A020.aspx
Battery Data M139_D2_10A_A021_A024.aspx
Battery Data M139_D2_10A_B01_B04.aspx
Battery Data M139_D2_10A_B05_B08.aspx
Battery Data M139_D2_10A_B09_B012.aspx
Battery Data M139_D2_10A_B013_B016.aspx
Battery Data M139_D2_10A_B017_B020.aspx
Battery Data M139_D2_10A_B021_B024.aspx
Battery Data M139_D2_10A_C01_C04.aspx
Battery Data M139_D2_10A_C05_C08.aspx
Battery Data M139_D2_10A_C09_C012.aspx
Battery Data M139_D2_10A_C013_C016.aspx
Battery Data M139_D2_10A_C017_C020.aspx
Battery Data M139_D2_10A_C021_C024.aspx
Battery Data M139_D2_10A_D01_D04.aspx
Battery Data M139_D2_10A_D05_D08.aspx
Battery Data M139_D2_10A_D09_D012.aspx
Battery Data M139_D2_10A_D013_D016.aspx
Battery Data M139_D2_10A_D017_D020.aspx
Battery Data M139_D2_10A_D021_D024.aspx
Battery Data M139_D2_10A_E01_E04.aspx
Battery Data M139_D2_10A_E05_E08.aspx
Battery Data M139_D2_10A_E09_E012.aspx
Battery Data M139_D2_10A_E013_E016.aspx
Battery Data M139_D2_10A_E017_E020.aspx
Battery Data M139_D2_10A_E021_E024.aspx
Battery Data M139_D2_10A_F01_F04.aspx
Battery Data M139_D2_10A_F05_F08.aspx
Battery Data M139_D2_10A_F09_F012.aspx
Battery Data M139_D2_10A_F013_F016.aspx
Battery Data M139_D2_10A_F017_F020.aspx
Battery Data M139_D2_10A_F021_F024.aspx
Battery Data M139_D2_10A_G01_G04.aspx
Battery Data M139_D2_10A_G05_G08.aspx
Battery Data M139_D2_10A_G09_G012.aspx
Battery Data M139_D2_10A_G013_G016.aspx
Battery Data M139_D2_10A_G017_G020.aspx
Battery Data M139_D2_10A_G021_G024.aspx
Battery Data M139_D2_10A_H01_H04.aspx
Battery Data M139_D2_10A_H05_H08.aspx
Battery Data M139_D2_10A_H09_H012.aspx
Battery Data M139_D2_10A_H013_H016.aspx
Battery Data M139_D2_10A_H017_H020.aspx
Battery Data M139_D2_10A_H021_H024.aspx
Battery Data M139_D2_10A_I01_I04.aspx
Battery Data M139_D2_10A_I05_I08.aspx
Battery Data M139_D2_10A_I09_I012.aspx
Battery Data M139_D2_10A_I013_I016.aspx
Battery Data M139_D2_10A_I017_I020.aspx
Battery Data M139_D2_10A_I021_I024.aspx
Battery Data M139_D2_10A_J01_J04.aspx
Battery Data M139_D2_10A_J05_J08.aspx
Battery Data M139_D2_10A_J09_J012.aspx
Battery Data M139_D2_10A_J013_J016.aspx
Battery Data M139_D2_10A_J017_J020.aspx
Battery Data M139_D2_10A_J021_J024.aspx
Battery Data M139_D2_10B_A01_A04.aspx
Battery Data M139_D2_10B_A05_A08.aspx
Battery Data M139_D2_10B_A09_A012.aspx
Battery Data M139_D2_10B_A013_A016.aspx
Battery Data M139_D2_10B_A017_A020.aspx
Battery Data M139_D2_10B_A021_A024.aspx
Battery Data M139_D2_10B_B01_B04.aspx
Battery Data M139_D2_10B_B05_B08.aspx
Battery Data M139_D2_10B_B09_B012.aspx
Battery Data M139_D2_10B_B013_B016.aspx
Battery Data M139_D2_10B_B017_B020.aspx
Battery Data M139_D2_10B_B021_B024.aspx
Battery Data M139_D2_10B_C01_C04.aspx
Battery Data M139_D2_10B_C05_C08.aspx
Battery Data M139_D2_10B_C09_C012.aspx
Battery Data M139_D2_10B_C013_C016.aspx
Battery Data M139_D2_10B_C017_C020.aspx
Battery Data M139_D2_10B_C021_C024.aspx
Battery Data M139_D2_10B_D01_D04.aspx
Battery Data M139_D2_10B_D05_D08.aspx
Battery Data M139_D2_10B_D09_D012.aspx
Battery Data M139_D2_10B_D013_D016.aspx
Battery Data M139_D2_10B_D017_D020.aspx
Battery Data M139_D2_10B_D021_D024.aspx
Battery Data M139_D2_10B_E01_E04.aspx
Battery Data M139_D2_10B_E05_E08.aspx
Battery Data M139_D2_10B_E09_E012.aspx
Battery Data M139_D2_10B_E013_E016.aspx
Battery Data M139_D2_10B_E017_E020.aspx
Battery Data M139_D2_10B_E021_E024.aspx
Battery Data M139_D2_10B_F01_F04.aspx
Battery Data M139_D2_10B_F05_F08.aspx
Battery Data M139_D2_10B_F09_F012.aspx
Battery Data M139_D2_10B_F013_F016.aspx
Battery Data M139_D2_10B_F017_F020.aspx
Battery Data M139_D2_10B_F021_F024.aspx
Battery Data M139_D2_10B_G01_G04.aspx
Battery Data M139_D2_10B_G05_G08.aspx
Battery Data M139_D2_10B_G09_G012.aspx
Battery Data M139_D2_10B_G013_G016.aspx
Battery Data M139_D2_10B_G017_G020.aspx
Battery Data M139_D2_10B_G021_G024.aspx
Battery Data M139_D2_10B_H01_H04.aspx
Battery Data M139_D2_10B_H05_H08.aspx
Battery Data M139_D2_10B_H09_H012.aspx
Battery Data M139_D2_10B_H013_H016.aspx
Battery Data M139_D2_10B_H017_H020.aspx
Battery Data M139_D2_10B_H021_H024.aspx
Battery Data M139_D2_10B_I01_I04.aspx
Battery Data M139_D2_10B_I05_I08.aspx
Battery Data M139_D2_10B_I09_I012.aspx
Battery Data M139_D2_10B_I013_I016.aspx
Battery Data M139_D2_10B_I017_I020.aspx
Battery Data M139_D2_10B_I021_I024.aspx
Battery Data M139_D2_10B_J01_J04.aspx
Battery Data M139_D2_10B_J05_J08.aspx
Battery Data M139_D2_10B_J09_J012.aspx
Battery Data M139_D2_10B_J013_J016.aspx
Battery Data M139_D2_10B_J017_J020.aspx
Battery Data M139_D2_10B_J021_J024.aspx
User avatar
atnbueno
Posts: 85
Joined: 12 Oct 2013, 04:45

Re: Copy different texts from excel into different files

14 Apr 2015, 17:44

Ouch. This looks like an X-Y Problem :roll:

Let's try something else. Forget Excel for a moment. Can you tell us the original XML and the desired output corresponding to the first file? I'm guessing the XML for the first file it's 4 <Table1> tags. Feel free to trim whatever sensitive text may be in Battery Data M139_D2_10A_A01_A04.aspx.

Meanwhile, this is how I was generating the filenames with AutoHotkey (press ESC when you get tired of messages, otherwise there'll be 600 of them):

Code: Select all

Letters := "ABCDEFGHIJ"
Loop, Parse, Letters
{
	Letter1 := A_LoopField
	Loop, Parse, Letters
	{
		Letter2 := A_LoopField
		Loop, 6
		{
			To := A_Index * 4
			From := To - 3
			MsgBox, % "Battery Data M139_D2_10" Letter1 "_" Letter2 "0" From "_" Letter2 "0" To ".aspx"
		}
	}
}

Esc::
	ExitApp
Return
This was before seeing your XML. If we end up reading XML it will be even easier: just one loop instead of three.


Regards,
Antonio
NoviceUser
Posts: 7
Joined: 13 Apr 2015, 20:25

Re: Copy different texts from excel into different files

14 Apr 2015, 19:30

Antonio thanks for the script, looks like it just generated a popup box with the next file name in the list, but didn't make any copies or the original or anything. Below is an excerpt of what i am trying to replace in each file. The files have already been copied and renamed accordingly just using excel and concatenate formula which i then copied to a .txt file that the .bat i used referenced (Pasted below). but your way did look alot easier for creating the file naming list itself if that could have just copied the 1st file and renamed based on your loops there. So in excel I took the instance number and added INSTANCE"###" around it as just finding like number 244 could show up in multiple places that i dont want to change it but INSTANCE"244" would only show up in the one place and replace would only affect that exact nomenclature. The stuff i pasted above with the names and instance numbers to be replaced was straight from the .xlm file itself not a tab separated value or anything, it just didn't copy over the cell borders. Also could more like like an XYZABCD problem as i have tried so many different ways to complete the task i could be WAYYYY throwing you off and for that i apologize.

<Cust:TMultiImage
runat="server"
src="./images/GreenNormal.png"
SITEID="-1"
SITENAME="VZWNEC2A"
TESSITENAME="<default>"
OBJECTNAME="M139_D2_10A UnitA04.Cell_State"
OBJECTTYPE="13"
INSTANCE="244"
DEVICE="2108"
PROPERTYID="85"
ARRAYINDEX="-1"
AspectRatio="0.195652173913043"
IMAGEVALUE="./images/GreenNormal.png|./images/OrangeWarning.png|./images/RedAlarm.png"
HIGHRANGE="3"
LOWRANGE="1"
BREAKVALUE="1.666667|2.333333"
APPLYALGO="Threshold"
TEMPLATEID=""
TEMPLATENAME=""
activeCheckState=""
tgeInternalSiteId="1"
tgeInternalDevSiteId="1"
tgeInternalDeviceId="2108"
tgeInternalCategoryId="0"
style="position:absolute;top:566px;left:849px;z-index:55;height:18px;width:92px;"
>
</Cust:TMultiImage >

<Cust:TText
runat="server"
SITEID="-1"
SITENAME="VZWNEC2A"
TESSITENAME="<default>"
OBJECTNAME="M139_D2_10A UnitA04.Cell_State"
OBJECTTYPE="13"
INSTANCE="244"
DEVICE="2108"
PROPERTYID="85"
INCLUDEUNITS="1"
ARRAYINDEX="-1"
APPLYALGO="Threshold"
TEMPLATEID=""
TEMPLATENAME=""
tgeInternalSiteId="1"
tgeInternalDevSiteId="1"
tgeInternalDeviceId="2108"
tgeInternalCategoryId="0"
style="position:absolute;top:566px;left:849px;z-index:56;height:18px;width:92px;background-color:transparent;font-size:10pt;font-family:Arial;font-weight:bold;color:#000000;border:#FFFFFF 2px inset;"
>
</Cust:TText>
<Cust:TMultiImage
runat="server"
src="./images/GreenNormal.png"
SITEID="-1"
SITENAME="VZWNEC2A"
TESSITENAME="<default>"
OBJECTNAME="M139_D2_10A UnitA01.Cell_State"
OBJECTTYPE="13"
INSTANCE="241"
DEVICE="2108"
PROPERTYID="85"
ARRAYINDEX="-1"
AspectRatio="0.195652173913043"
IMAGEVALUE="./images/GreenNormal.png|./images/OrangeWarning.png|./images/RedAlarm.png"
HIGHRANGE="3"
LOWRANGE="1"
BREAKVALUE="1.666667|2.333333"
APPLYALGO="Threshold"
TEMPLATEID=""
TEMPLATENAME=""
activeCheckState=""
tgeInternalSiteId="1"
tgeInternalDevSiteId="1"
tgeInternalDeviceId="2108"
tgeInternalCategoryId="0"
style="position:absolute;top:365px;left:527px;z-index:57;height:18px;width:92px;"
>
</Cust:TMultiImage >

This is the .bat file contents

SET source_file=Battery Data M139_D2_10A_A01_A04.aspx
SET name_list_file=Filenames.txt

FOR /F "usebackq delims=," %%G IN (`TYPE %name_list_file%`) DO (
COPY "%source_file%" "%%G"
)
PAUSE
NoviceUser
Posts: 7
Joined: 13 Apr 2015, 20:25

Re: Copy different texts from excel into different files

14 Apr 2015, 19:53

Here is the task i was given, short simple and sweet. Create 160 graphics pages all looking exactly the same, just with different data on each page. Each page will show the Cell state, impedance and voltage of 4 batteries in a large UPS databank. The .aspx is just a database file that is like an HTLM hybrid type file a SQL server looks at. I went into the database, exported the point names and their associated instance number (The instance # is what SQL looks at, the name is for the end user to make sense of the number) I just filtered and sorted the data, trimmed off all the excess stuff the graphic page doesn't look at or care about, and thats the stuff i need to replace for each file that was copied. I was given the time to expedite this task as it is quite common, and will be done again in the future just with different layouts, point names, file names etc...
User avatar
Blackholyman
Posts: 1292
Joined: 29 Sep 2013, 22:57
Facebook: socialjsz
Google: +Jszapp
Location: Denmark
Contact:

Re: Copy different texts from excel into different files

15 Apr 2015, 10:09

I tried to read all of your info on my phone but may have mist some of it...

What we are trying to get from you is not you bat file or what you have done to the data...

We are trying to tell you that if you can show us the plain data as it looks when you export it

Then you (with the help of us) can make all of the things (files with the right text from the start) you don't need to make the files before hand as autohotkey can make all that happen!

We just need to know what the first plain data looks like and what you need it to end up looking like...

If you have all ready given that info i'm sorry but i could not put it together While reading it on my phone.

We just hope to give you the best, easyer to reuse script.
Also check out:
Courses on AutoHotkey

My Autohotkey Blog
:dance:
NoviceUser
Posts: 7
Joined: 13 Apr 2015, 20:25

Re: Copy different texts from excel into different files

15 Apr 2015, 11:47

Sorry for the confusion as you can tell I'm quite brain fried from this. It's there a way I can attach the files somehow as theyare quite large. I'll send the raw database file and the original page I'm trying to duplicate.
User avatar
Blackholyman
Posts: 1292
Joined: 29 Sep 2013, 22:57
Facebook: socialjsz
Google: +Jszapp
Location: Denmark
Contact:

Re: Copy different texts from excel into different files

15 Apr 2015, 13:35

It's no problem we all start somewhere, the forum will no longer let you upload things (space issue i think)

You will need to upload them somewhere else like dropbox, copy, pastebin, others

And give us a link(s) to the file(s)
Also check out:
Courses on AutoHotkey

My Autohotkey Blog
:dance:
NoviceUser
Posts: 7
Joined: 13 Apr 2015, 20:25

Re: Copy different texts from excel into different files

15 Apr 2015, 19:13

Hope this works, attached is the raw data from the database (Datapoints.xml) list of the file names i am needing, i created that list crudely, so if can be done with AHK easier then please don't let the fact its already there stop you geniuses! Last if the graphic page i am trying to duplicate.

http://jmp.sh/nGPzlgp

If this doesn't work just let me know and i will goto dropbox, didn't want to install most software if i didn't need to why i went here.

Thank you for your patience with my noobness! :D
User avatar
Blackholyman
Posts: 1292
Joined: 29 Sep 2013, 22:57
Facebook: socialjsz
Google: +Jszapp
Location: Denmark
Contact:

Re: Copy different texts from excel into different files

17 Apr 2015, 17:42

hi your Xml file gives me a parsing error, looks to be some unicode char not supported by MSXML2.DOMDocument.6.0

Sorry don't have any more time at the moment...
Also check out:
Courses on AutoHotkey

My Autohotkey Blog
:dance:
NoviceUser
Posts: 7
Joined: 13 Apr 2015, 20:25

Re: Copy different texts from excel into different files

19 Apr 2015, 01:11

Ya i had the same problems, this is not a true 'webpage' but rather just a .xlm file that my proprietary software uses in conjunction with a sql database. I have been opening the .xml files in notepad just FYI. Thank you again for your efforts.

Return to “Ask For Help”

Who is online

Users browsing this forum: boiler, Chunjee, Lem2001, mikeyww, thebat, XMCQCX and 47 guests