Data Conversion

Looking for a program that can automatically manipulate fields in data exported files. The exported .csv files display the "Date" as: 02/28/06 and charting software needs date to be : 060228. Changing the files manually is rather tedious.

Any ideas?

 

maybe copy the data into excel, change it, the export to your software?

in excel you can reformat it
number format > custom> yymmdd

 

This can be easily done with a PHP script.

 

A "better" way would be to create a VBA (via Excel) macro to do it all... import, modify, and export. One click.

Difficulty
With VB/Excel skills... 3
No VB/Excel skills ... 10

 

Just remembered a freeware proggie that'll do the job, no muss-fuss. Actually it's cardware... send the author a postcard of your town.

CSVed
http://home.hccnet.nl/s.j.francke/t2t/text2table.htm

Has a specific edit for changing date-format of a specified column.

Osorico

 

If you have a bit of programming experience, this sort of task is perfect for Qt - you can easily make a simple GUI and code up the necessary string manipulations. In fact, I suspect it might be easier to accomplish using QRegExp but I am not experienced with QRegExp.

You can download the open source version of the library here:

http://www.trolltech.com/download/opensource.html

A simple GUI app with a few pushbuttons that allows you to select multiple .CSV or .TXT files, automatically make the necessary changes to your input files and write to new output files is about 100 lines of code:

Code:

#include < QApplication >
#include < QtGui >

class MainWidget : public QWidget
{
  Q_OBJECT

public:
	MainWidget(QWidget * parent = 0);

private:
	QPushButton * b1;
	QPushButton * b2;
	QListWidget * l;
	QStringList files;

private slots:
	void selectFiles();
	void convertFiles();

};

MainWidget::MainWidget(QWidget * parent) : QWidget(parent)
{
	b1 = new QPushButton(tr("Select files to convert"));
	b2 = new QPushButton(tr("Convert selected files"));
	l = new QListWidget;
	l->setFixedWidth(360);
	l->setFixedHeight(250);

	QVBoxLayout * v = new QVBoxLayout;
	v->addWidget(b1);
	v->addWidget(b2);
	v->addStretch(0);

	QHBoxLayout * h = new QHBoxLayout;
	h->addLayout(v);
	h->addWidget(l);

	setLayout(h);
	
	connect(b1, SIGNAL(clicked()), this, SLOT(selectFiles()));
	connect(b2, SIGNAL(clicked()), this, SLOT(convertFiles()));
}

void MainWidget::selectFiles()
{
  files = QFileDialog::getOpenFileNames(this, 
    "Select one or more CSV files for date format conversion", 
    "./", "CSV Files (*.csv *.txt)"); 
  l->clear(); 
  l->addItems(files);
}

void MainWidget::convertFiles()
{
  if (!files.isEmpty())
  { 
    for (int i = 0; i < files.size(); ++i) {
    QString inputline;

    QString newpath = files.at(i);
		
    QFile inputfile = newpath;
    inputfile.open(QIODevice::ReadOnly | QIODevice::Text);
    QByteArray bytearray = inputfile.readAll();
    QTextStream in(bytearray);

    int lastperiod = newpath.lastIndexOf(".");
    if (lastperiod != -1)
      newpath.insert(lastperiod, QString("_out"));  
    else
      newpath.append(QString("_out"));
    QFile outputfile = newpath;
    outputfile.open(QIODevice::WriteOnly 
      | QIODevice::Text); //overwrite output file if exists..
    QTextStream out(&outputfile);
			
    while (!in.atEnd()) {
     inputline = in.readLine();	
     int a = inputline.indexOf("/");
     int b = inputline.indexOf("/",a+1);
     int firstcomma = inputline.lastIndexOf(",", a);
     int commaafterdate = inputline.indexOf(",", b+1);

     if (a != -1 && b != -1) {
      QString beginning;
      if (firstcomma < a && firstcomma > 0) {
       beginning = inputline.mid(0,firstcomma+1);
       firstcomma++;
      }
      else {
       beginning = QString("");
       firstcomma = 0;
      }
      QString end = inputline.mid(commaafterdate,inputline.size()-commaafterdate);
      QString yy = inputline.mid(b+1,commaafterdate-b-1);
      QString dd = inputline.mid(a+1,b-a-1);
      if (dd.size() == 1)
       dd.insert(0, "0");
      QString mm = inputline.mid(firstcomma,a-firstcomma);
      if (mm.size() == 1)
       mm.insert(0, "0");
      QString outputline = beginning + yy + mm + dd + end + QString("\n");
      out << outputline;
     }
     else {
      inputline.append("\n");
      out << inputline;
     }
    }
    inputfile.close();
    outputfile.close();
    }
  }
}

#include "main.moc"
int main(int argc, char *argv[])
{
  QApplication app(argc, argv);
  MainWidget widget;
  widget.show();
  return app.exec();
}

The code is 100% platform independent! It will compile and run, without change, on any of the supported Qt platforms.

I kind of just threw this together - it should work correctly but if you happen to download Qt and compile this (probably using the MinGW version if you are on Windows - nice and easy) and it does not work properly, PM me and I'll happily fix any mistakes.


ps - Dear ET Moderator: if you could, please kindly contact the appropriate people to suggest the following: any tags (such as using < and >) that are inside of

Code:

blocks should probably be ignored..

 

Thanks to all for help. Got CSVed running and it works well. D/L'd Qt but couldn't figure out how to compile the code EliteInterest so graciously provided. Qt is a little confusing for a non-programmer techno-peasant like me

 

Hi Stardust,

I am happy you found a solution that works for you.

Just for completeness, here are some additional details to get the program going. I realize you do not need to do this now, but just in case anyone reading this is interested in trying Qt..

- On Winows, download and install the file: qt-win-opensource-4.1.2-mingw.exe. This package will download and install the MinGW compiler for you along with installing the Qt library.

- Copy the source into a text file called 'main.cpp' and save it into a
directory off your Qt installation directory (I create a 'PROJECTS' folder) - so for example you might save it as:
C:\Qt\4.1.2\PROJECTS\DateCsvConversion\main.cpp

- Edit the file and remove the spaces in between the < >'s in the first two lines of code (I had to put the spaces there in order for the lines to show up at all).. also change the lines that say QFile inputfile = newpath; and QFile outputfile = newpath; to QFile inputfile(newpath); and QFile outputfile(newpath); (sorry about that - these changes to the QFile lines will work with both the MinGW and the Visual Studio compiler..).

- Make sure you use the command line prompt provided in the Trolltech section of your 'start menu'. This will ensure that the appropriate environment variables are set, in case you elected not to set them during installation.

- Using this command prompt, change to the directory where your source file is located and type three commands:
qmake -project
qmake
make

And that's it. The executable is in the ./release directory. I just tried it and it works.. Sorry for the confusion.

The example is rudimentary but perhaps a reader might find some inspiration from this and wish to create their own custom projects.. just PM me if you cannot get Qt going or the code won't compile, I'll be happy to help out.

 

Indeed, Qt id the greatest.
I used it a lot under windoz.
These days, only with linux.
Perfect portability. No gui framework can rival with it.
Good with C/C++, also Python (PyQt)

 

echo 02/28/06| perl -MDate::Manip -ne 'print Date::Manip::UnixDate(Date::Manip:arseDate($_),"%y%m%d")'