Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » Community » Newbie corner » Read txt, analyze and convert to csv
Read txt, analyze and convert to csv [message #53183] Sun, 15 March 2020 16:52 Go to next message
idkfa46 is currently offline  idkfa46
Messages: 139
Registered: December 2011
Experienced Member
Hallo guys,
how are you? Maybe someone remember me... I have not been coding for a long time and I'm back to you for an help with my new project.

To speed up a working activity I have to Open a positional TXT, I have to analyse it and export it as a CSV. I'm used to import and elaborate it in Access but the file is everyday bigger and it is going excede Access limits in a short time so I was thinking about a dedicate tool. Is it something I can easy do with UPP?

It is a positional txt with 650 character for line and about 800k lines at the moment (and I have to skip the first and last line)...

Any suggestion about the right approach to the project?
Someone can help me to start this project?

Best regards,
Matteo
Re: Read txt, analyze and convert to csv [message #53192 is a reply to message #53183] Tue, 17 March 2020 09:45 Go to previous messageGo to next message
omari is currently offline  omari
Messages: 220
Registered: March 2010
Experienced Member
you can start with this:
#include <CtrlLib/CtrlLib.h>

using namespace Upp;

Vector<String> SpliteLine(String& line)
{
	// example
	return Split(line, "\t", false);
}

GUI_APP_MAIN
{
	String s = LoadFile(TXT_file_path);
	StringStream ss(s);
	FileOut out(csv_file_path);
	while(!ss.IsEof() && !ss.IsError())
	{
		String line = ss.GetLine();
		Vector<String> fields = SpliteLine(line);
		if(!fields.GetCount()) continue;
		String r = CsvString(fields[0]);
		for(int i = 1; i < fields.GetCount(); i++)
			r << ";" << CsvString(fields[i]);
		
		r << "\n";
		
		out << r;
	}
}



regards
omari.
Re: Read txt, analyze and convert to csv [message #53202 is a reply to message #53192] Wed, 18 March 2020 13:27 Go to previous messageGo to next message
idkfa46 is currently offline  idkfa46
Messages: 139
Registered: December 2011
Experienced Member
Thank you, Omari!
Now I can open, read my txt and export my csv.

The only problem is that each line of my txt has a structure like this:

X - Field name - Type - Pos. - Length
1 - Distribution Type - A/N - 1 - 3
2 - Distribution date - A/N - 4 - 8
3 - Application date - A/N - 12 - 8
4 - Sales organization - A/N - 20 - 4

and I have to split each line following "Pos" and Length rules.

Thanks,
Matteo

Is there any dedicate solution to do it?
Re: Read txt, analyze and convert to csv [message #53203 is a reply to message #53202] Wed, 18 March 2020 17:31 Go to previous messageGo to next message
omari is currently offline  omari
Messages: 220
Registered: March 2010
Experienced Member
Vector<String> SpliteLine(String& line)
{
       ASSERT(line.GetCount() >= 23);
	Vector<String> r;

        r.Add() = line.Mid(0, 3);
        r.Add() = line.Mid(3, 8);
        r.Add() = line.Mid(11, 8);
        r.Add() = line.Mid(19, 4);

	return r;
}


regards
omari.
Re: Read txt, analyze and convert to csv [message #53208 is a reply to message #53203] Fri, 20 March 2020 09:40 Go to previous messageGo to next message
idkfa46 is currently offline  idkfa46
Messages: 139
Registered: December 2011
Experienced Member
Good! thanks you so much!

The while cicle take a lot of time because the file I'm going to read is 800k rows so I decided to introduce a ProgressIndicator to monitor it. The problem is that at the moment the progress indicator appear only at the end of the process so it is useless... Why?

PriceListGenerator::PriceListGenerator()
{
	CtrlLayout(*this, "PriceList Generator");
	
	//menĂ¹-tool-status
	AddFrame(menu);
	AddFrame(tool);
	AddFrame(status);
	menu.WhenHelp = status;
	tool.WhenHelp = status;
	menu.Set(THISBACK(MainMenu));
	tool.Set(THISBACK(SubBar));
	
	//Progress
	status.Add(pi.RightPos(5, 200).TopPos(2, 15));   //Indicator inside my statusBar
	pi.Percent();
	pi.Hide();

	//String s = LoadFile(TXT_file_path);
	generate <<= THISBACK(LoadPriceList);
	//LoadPriceList(s);
}

void PriceListGenerator::LoadPriceList()
{
	String s = LoadFile("C:\\Users\\Matteo\\Desktop\\prova.txt");
	StringStream ss(s);
	
	int x = 0;
	pi.Show();
	//pi.Set(x,1000); //how can I get the right number of rows?
	
	FileOut out("C:\\Users\\Matteo\\Desktop\\output.csv");
	out << AddHeader();
	
	while(!ss.IsEof() && !ss.IsError())
	{
		String line = ss.GetLine();
		Vector<String> fields = SpliteLine(line);
                pi.Set(x++);                       // ProgressIndicator
		
		if(!fields.GetCount()) continue;
		
		String r = CsvString(fields[0]);
		for(int i = 1; i < fields.GetCount(); i++)
		{
			r << ";" << CsvString(fields[i]);
		}
		r << "\n";
		out << r;
	}
}


It could be a problem about statusBar?
Moreover, if I would like to show the percentage how can I get the total number of rows?

Best,
Matteo
Re: Read txt, analyze and convert to csv [message #53219 is a reply to message #53208] Mon, 23 March 2020 13:21 Go to previous messageGo to next message
idkfa46 is currently offline  idkfa46
Messages: 139
Registered: December 2011
Experienced Member
Any idea?

Matteo
Re: Read txt, analyze and convert to csv [message #53233 is a reply to message #53219] Tue, 24 March 2020 15:20 Go to previous messageGo to next message
omari is currently offline  omari
Messages: 220
Registered: March 2010
Experienced Member
1 - you can call
ProcessEvents();
after the call
 if (x % 200 == 0) pi.Set(x++);   


2 -
Quote:
pi.Set(x,1000); //how can I get the right number of rows?


pi.Set(x, s.GetCount() / lineSize); 


in your case, i think, lineSize = 24.


regards
omari.

[Updated on: Tue, 24 March 2020 16:57]

Report message to a moderator

Re: Read txt, analyze and convert to csv [message #53236 is a reply to message #53233] Tue, 24 March 2020 15:52 Go to previous messageGo to next message
idkfa46 is currently offline  idkfa46
Messages: 139
Registered: December 2011
Experienced Member

You rock!
thank you Omari!
Re: Read txt, analyze and convert to csv [message #53281 is a reply to message #53183] Wed, 25 March 2020 20:26 Go to previous messageGo to next message
idkfa46 is currently offline  idkfa46
Messages: 139
Registered: December 2011
Experienced Member
Going ahead with the development of my tool I discovered this strange situation:

here is and example my csv output:
"20200103";"0113E0            ";"SEMICUSCINETTO                ";"00000001594";"H ";Common part
"20200103";"0113E1            ";"SEMICUSCINETTO                ";"00000001582";"H ";Common part
"20200103";"0113E2            ";"SEMICUSCINETTO                ";"00000002264";"H ";Common part
"20200103";"0113E3            ";"SEMICUSCINETTO                ";"00000001667";"H ";Common part
"20200103";"0113E4            ";"SEMICUSCINETTO                ";"00000001667";"H ";Common part


The problem is that opening it with microsoft Excel the are part numbers like "0113E2 " are automatically converted in a scientific format and becaome 1,13E+02...

Maybe the solution to force it like a Text should be to include a ' at the beginning of the number...

What do you think?

Best,
Matteo
Re: Read txt, analyze and convert to csv [message #53283 is a reply to message #53281] Thu, 26 March 2020 10:50 Go to previous messageGo to next message
idkfa46 is currently offline  idkfa46
Messages: 139
Registered: December 2011
Experienced Member
The ' doesn't work! Confused

Matteo
Re: Read txt, analyze and convert to csv [message #53364 is a reply to message #53283] Tue, 31 March 2020 09:57 Go to previous messageGo to next message
idkfa46 is currently offline  idkfa46
Messages: 139
Registered: December 2011
Experienced Member
Is there a way to convert my CSV in XLS file formatting all cells a text?
Following the Excel import procedure and setting columns as Text I can avoid the data conversion...

Best,
Matteo
Re: Read txt, analyze and convert to csv [message #53371 is a reply to message #53364] Tue, 31 March 2020 15:17 Go to previous messageGo to next message
omari is currently offline  omari
Messages: 220
Registered: March 2010
Experienced Member
try this solution : (putting '=' at the beginning )

"20200103";="0113E0            ";"SEMICUSCINETTO                ";"00000001594";"H ";Common part
"20200103";="0113E1            ";"SEMICUSCINETTO                ";"00000001582";"H ";Common part
"20200103";="0113E2            ";"SEMICUSCINETTO                ";"00000002264";"H ";Common part
"20200103";="0113E3            ";"SEMICUSCINETTO                ";"00000001667";"H ";Common part
"20200103";="0113E4            ";"SEMICUSCINETTO                ";"00000001667";"H ";Common part


regards
omari.
icon14.gif  Re: Read txt, analyze and convert to csv [message #53376 is a reply to message #53371] Tue, 31 March 2020 16:35 Go to previous message
idkfa46 is currently offline  idkfa46
Messages: 139
Registered: December 2011
Experienced Member

Outstanding!

Thanks,
Matteo

Previous Topic: [SOLVED] Cloning Array of complexe type
Next Topic: OptionTree inside a DropTree
Goto Forum:
  


Current Time: Sat Jun 06 19:36:01 CEST 2020

Total time taken to generate the page: 0.00892 seconds