-1

I have a .txt file with rows of the following format

SI1334596|MRKU3|High Cube|1|EGST|First Line|Vehicle one|25|13|

How do I form a database of above .txt entries to perform SQL queries on it? I also want to assign column names to each of the columns. I have little or no knowledge on importing txt file entries in a database. I am looking for a software which can be installed on my windows computer which can import .txt file and convert into a database and allow me to perform queries thereafter.

Karvy1
  • 712
  • 4
  • 9
  • 24
  • 2
    Which database:mysql,sql server?Sql is just the language – Mihai Jul 04 '15 at 18:53
  • Not specific. I just want to perform SQL queries on the database formed. You are welcome to recommend one. – Karvy1 Jul 04 '15 at 18:55
  • 1
    Look into mysql,convert that txt to csv and read here http://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile MIght be as simple as opening it into word and save it as .csv – Mihai Jul 04 '15 at 18:57
  • Save as csv(comma delimited)? There are few more options to save with csv format – Karvy1 Jul 04 '15 at 19:06
  • May I know why my question is still down-voted? @Mihai – Karvy1 Jul 16 '15 at 23:19
  • I didnt downvote you but people might think this is too broad. – Mihai Jul 17 '15 at 06:15
  • Okay. It seems my question was not properly formatted. I just edited the question with proper formatting. – Karvy1 Jul 17 '15 at 20:01

1 Answers1

1

If you are asking for recommendations on specific tools, then your Question is off-topic for StackOverflow.com. See the Software Recommendations Stack Exchange.

Here are some possible approaches, with and without programming.

Database Import

Databases often have a built-in command or facility for importing data straight from a text file. When directly importing text with little or processing, the import is often very fast.

For example, Postgres has the copy command to import. This command includes a parameter DELIMITER where you can tell it to expect the vertical bar | as the separator between fields.

You would define your table structure ahead of time, before the import, defining a name and data type for each expected column/field.

Custom App

You can write an app to read the text file, process the incoming data, and feed the prepared data to the database. For example, write a Java app that reads the text file, uses JDBC to connect to the server, and SQL written as text strings to instruct the database server on what to do.

You can do this row by row. Or, for increased speed, you can write a batch statement telling the database server to create multiple rows at the same time.

This is the way to go if the data requires complicated processing or there are other related chores such as keeping a history of many such imports, logging other information, reporting duplicate data, and so on.

For Java, the Apache Commons CSV library helps with reading/writing plain text files.

Spreadsheet

Many spreadsheets, such as LibreOffice Calc, can parse the data, deduce the column headers as titles, and populate a spreadsheet. You can do queries within the spreadsheet. Works well for smaller amounts of data that can comfortably reside within memory. You may not need a database at all.

Database Tool

SQL database engines such as Postgres, H2, SQLite, and MySQL/MariaDB are just black-box engines not full-blown interactive data tools. You can obtain such tools that connect with these engines. This tools can import/export text files, display lists of data, let you enter/modify data, create forms for better access to the data, and generate reports.

But there are some such data tools that have a database engine built-in. Examples include:

Community
  • 1
  • 1
Basil Bourque
  • 218,480
  • 72
  • 657
  • 915