June 16, 2017
Hi to everyone,
I'm a novice who occasionally uses Excel (I have version 2013) and I have a problem that I can not solve without the help of a script (I think).
I have, in a CSV file, a table consisting of 2 columns (the second column (after </option> is the number)
For example, the original CSV file:
<option>ALPHA</option>
<option value="gfhjdf">1 </option> 2.17
<option value="add">4 </option> 2.45
<option value="ggsssdf">12</option> 2.69
<option value="ggkgkjgk">6 </option> 2.61
<option>BETA</option>
<option value="hggfg">199 </option> 0.9803
<option value="jkhgjg">235 </option> 2.425
<option value="hkjgjh">1100 </option> 2.65
<option>GAMMA</option>
<option value="rjasaf">251 </option> 1.55
<option value="fhfa">1457 </option> 1.81
<option value="oith">1 444</option> 1.18
<option value="ghgjglg">9 </option> 0.7964
<option value="hdjjdjghg">4555 </option> 1.15
<option value="gfsdssm,.">98971 </option> 2.1999
<option>THETA</option>
<option value="otiutre">4444 </option> 1.65
<option value="qrtewq">46413 </option> 2.7
<option value="pjhn,,g">98714 </option> 1.05
<option value="dryjd">646542 </option> 1.0499
<option value="hfjjffk">45641 </option> 1.24
<option value="sdsghj">4641</option> 0.86
<option value="xbxn">5467 </option> 1.9
<option>OMEGA</option>
<option value="fhhjf">2146554 </option> 1.5
<option value="cv,n,m">61332 </option> 1.17
<option value="fhfjd">461312 </option> 2.61
<option value="eywery">461432</option> 0.53
the result must be below:
A table
1. With CRESCENT NUMBER sorted in EVERY SECTION.
2. limit number condition (1.5) ,
3. WITHOUT NUMBER in the 2nd column (only first column)
4 this result must be automatically (if possible) exported in a new (or overwriting the original CSV) TXT file.
<option>ALPHA</option>
<option value="gfhjdf">1 </option> 0.55
<option value="add">4 </option> 1.09
<option>BETA</option>
<option value="hggfg">199 </option> 0.9803
<option>GAMMA</option>
<option value="ghgjglg">9 </option> 0.7964
<option value="hdjjdjghg">4555 </option> 1.15
<option value="oith">1 444</option> 1.18
<option>THETA</option>
<option value="sdsghj">4641</option> 0.86
<option value="dryjd">646542 </option> 1.0499
<option value="pjhn,,g">98714 </option> 1.05
<option value="hfjjffk">45641 </option> 1.24
<option>OMEGA</option>
<option value="eywery">461432</option> 0.53
<option value="cv,n,m">61332 </option> 1.17
PS: I'ts a simple routine to be put in a my html file in a a specified position.
(but that's non important)
can anyone help me?
Thanks in advance.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
June 16, 2017
sure.. can't see a button for attachement.(I'm new registered today)
click to download the sample in CSV format https://www.sendspace.com/file/pej6if
Do You want the result in CSV format like above?
Thanks for you reply. For any question I'm here.
Ki$$
I've forget to write.....
PS: for your help to find a solution, IF YOU WANT you can add below the last row of 1st column : SELECT with no more section after the last. (that's not important for me, you can remove last row if this is better for you because i can add it myself after)
PS2: the sections in the sample are 5, but could be more o less of 5.
PS3: I've found the attachment link 🙂
PS4: in the results link you see the 2nd column with number, but at the end must be only the 1st column.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi ,
Try the attached file. First, Make sure you install the Power Query add-in from Microsoft: https://www.microsoft.com/en-u.....x?id=39379
Before pressing the button to Import-Export, change the file path to the source data in the File Address sheet.
June 16, 2017
ok, there are some problem.
I will attach now 6 files
1. automaticTry: automatize all (please go to ths the script and change your parameter if any) in a rar file because can't upload a .vbs file
2 your file: SortAndExport.xlsm (I've modified import/export in your script from csv to txt )
3 mySample.txt (some sections from MY ORIGINAL FILE)
4 your result (results generated with automaticTry+SortAndExport )
...and the 2 files named:
----
5. RealResult.txt all section sorted by number (with number in B column for better compare with generated youResult file)
Now, if you compare YourResult file and RealResult file you will find some difference... " ... wrong sorting etc... .
PS: As told above, RealResult file now show B column (to better compare yourself the 2 file) but in the Final File must be ONLY ONE column (A)
----
PS2: is possible to have a condition, inner the script, to remove row above specified number (Variable) in the final file (example "cut row" above 1.50 value)?
6. RealFinal.txt result (with B column to better compare) and row (number condition) cutted ( FinalResult.txt have only the (A) column in the file.)
-----
...oh yes now you have 6 files, but reallly at the end will be only 2 files (mySample file and SortAndExport) and I must click only automaticTry to have the result.
PS 3: is possible to remove any request to save the file.... (writing or overwriting it directly without "want to save etc...")?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
June 16, 2017
no, you was right... only A column.
and then...
1. I've removed [ (&";"& (if [Column2]=null then "" else Text.From([Column2]))= ] in last istance on Power QUERY to remove ";" (It's ok?)
2. and I've removed the filter from custom.1 [in Sort Sheet] because don't show data in any field to export. (It's Ok?)
Now seem is ALL perfect in the [import and sort] but in the result [export] file there are ["] in every field. is possible to remove all?
PS: note that [in this example and in attachment]:
your resut is:
"<option value=""MVIS"">MVIS 51.49 0 GM</option>"
but result in every field MUST BE (MVIS have single "):
<option value="MVIS">MVIS 51.49 0 GM</option>
-------------------------------------------------------------------
After resolving the ["] above, I have last question: is there a way to remove the "request to save" the result in a file (save directly without any OK input from myself)?
Thanks for all your help.. Now I know "Power Query".. it's very useful 🙂
June 16, 2017
For ["] problem,
apparently I have fixed it changing FileFormat:=36 (from previous FileFormat:=xlCSV) in the script.
Is a good solution for you?
----------
if yes, now remain the latest question: is there a way to remove the "request to save" the result in a file (save directly without any input from myself)?
June 16, 2017
after "FileFormat:=36" (to resolve export problem), apparently... I have resolved last question of the previous post putting "Application.DisplayAlerts = False" in the 2nd row of the script.
Final script is below. It's all ok for you?
Thanks in advance for any reply.
Sub ImpExp()
Application.DisplayAlerts = False
Dim FilePath As String
ThisWorkbook.RefreshAll
Application.Wait Now() + TimeSerial(0, 0, 5)
Sheets("Result").UsedRange.ClearContents
Range(Range("B4"), Range("B4").End(xlDown)).Copy
Sheets("Result").Cells(1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
FilePath = Sheet1.Range("SourceFile[File Name]")
FilePath = Left(FilePath, InStrRev(FilePath, "\"))
Sheets("Result").Copy
ActiveWorkbook.SaveAs Filename:=FilePath & Format(Now(), "yyyy-mm-dd-hh-mm-ss") & "-Result.txt", _
FileFormat:=36, CreateBackup:=False
ActiveWindow.Close
End Sub
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
See if the attached file is working as expected.
Setting DisplayAlerts to false is redundant, the file name contains a unique time stamp, it's impossible to have the same file name twice, there will never be a message displayed.
FileFormat can be 36, of course, and -4158 as well (xlCurrentPlatformText)
The attached version is not using the SaveAs method, it is simply creating and writing a text file directly.
Sub ImportExport()
Dim FilePath As String, Rng As Range, Cell As Range, FileNo As Integer
ThisWorkbook.RefreshAll
Application.Wait Now() + TimeSerial(0, 0, 5)
FilePath = Sheet1.Range("SourceFile[File Name]")
FilePath = Left(FilePath, InStrRev(FilePath, "\"))
FilePath = FilePath & Format(Now(), "yyyy-mm-dd-hh-mm-ss") & "-Result.txt"
Set Rng = Range(Range("B4"), Range("B4").End(xlDown))
FileNo = FreeFile
Open FilePath For Output As #FileNo
For Each Cell In Rng
Print #FileNo, Cell
Next
Print #FileNo, "</select>"
Close #FileNo
End Sub
June 16, 2017
I tried to use xlCurrentPlatformText (not 36) but result file show again " everywhere.
anyway , your latest script work very well and now I use it.
PS: in the latest script I see a "Print" at the end to add the row </select>. For every row to add I must put Print?
Example: if I want to add 3 row after </select>
Hi
hello
bye
...
Next
Print #FileNo, "</select>"Print #FileNo, "Hi"
Print #FileNo, "hello"
Print #FileNo, "bye"
Close #FileNo
End Sub
If it's right, is there a simple way to add multiple (many) row without write Print for "ANY" row (or maybe to call an external file txt to add these multiple row)?
------------------------
PS2: in the some way as PS1, If I want to add (one or many) row "BEFORE" the first row in a Result file? I can use Print like above? If yes, where I can write print in the script?
That's only a question regarding PS1 and PS2 because your latest script is perfect and I'm very happy to have it. If is too much difficult to rewrite or change the script or spent too much time, tell me friendly because now is all OK (I can simply write myself a batch file to append text (before and after Result.txt to create new Html finished file)
Thanks again for all your help 🙂
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Yes, you can write before or after the loop from cells:
Print #FileNo, "Whatever"
For Each Cell In Rng
Print #FileNo, Cell
Next
Print #FileNo, ""
Print #FileNo, "Completed"
Print #FileNo, "Anything"
And yes, you can write text from other text files, you have to open them for reading, copy content and Print #FileNo MyData:
http://stackoverflow.com/quest...../20128115/
Dim MyData As String
Open "C:\MyFile" For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
1 Guest(s)