Import Fixed Lenght Field From Ascii Text
does anyone have some suggestion to import Asccii text into Mysql tables
The tables are empty and can be created at import
The records from ascii text are fixed lenght but delimited by CRLF
The fields are not delimited but fixed lenght
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Force Fixed Decimal On Import
I have a text file that is length delimited, I would like to import this into a table. I was successful in importing the data using "load data local infile '/name of file' into table tablename FIELDS ENCLOSED BY '' AND TERMINATED BY '' Some of the fields in the file are numerical with different lengths an should be formatted set to 2 decimal places(example 47000 to 470.00 ) I have tried setting the fields in the table as decimal (10,2) but this just adds two addtional places. Is there a way to format the data so that will place a decimal holder at 2.
ASCII-fy HEX String From TEXT Column
problem: table: hex_table (int) (text) +-----+---------- | id | hex_text +-----+---------- | 1 | 616263 ... (goes on for another ~2000 characters) +-----+---------- how do I query this table to output the hex_text column as ascii? (i.e. select .... from hex_table; returns 'abc') if I go: "SELECT 0x616263" mysql returns 'abc' as expected, but "SELECT concat('0x', hex_text) from hex_table; doesn't work. note that conversion is impossible because the string is very long. A solution (in mysql, not an outside script) to this problem.
Fixed Width Text Files
I need to import multiple fixed-width text files into a database on a daily basis. My thought was to do this with a stored procedure, but I don't see any mysql syntax that would allow me to load a fixed-width file into a table. What am I missing?
Import NULL Or Empty Field Field Value
If you are importing data from an external data source and you have the choice of populating empty fields with NULL, what are the pros and cons of doing so?
Import From Text
I have a text file which contains names and dates. the date is in the format Sep-11-2001 12:30 AM. I tried to put the text into Excel and then used Navicat to import from Excel. But everytime the dates are converted to 1/1/2001.
Text Import
I need to append records from an Excel spreadsheet to an existing table. I think I should start by converted the Excel sheet to a text file then importing from there. I have to do this using myPHPAdmin. Am I on the right path and how do I start and import using myPHPAdmin?
Insert Text File In A Text Field
2 questions: 1) How can I insert a text file into a TEXT field (of a table) by a mysql instruction ?; 2) How can I insert a microsoft word file into a BLOB field (of a table) by a mysql instruction?
CSV Import When There Are Commas In The Text?
I'm trying to grab a csv file from a remote server and import the data my mySQL DB. I'm using Curl to grab the file contents and php to import the data into my DB. The problem have is that the data in the csv file contains commas in the text field that is interferring with the import and I have no control over what is in the file. eg the csv looks like: id,name,description1,description2,date 1,pete,"this has a comma, in the text", "this also has, a comma", 2004-09-09 etc, etc The additional commas mess up the import. Is there any way I can get this data into mysql and just tell it to ignore any commas in certain fields?
How To Import Text File?
I am using MAMP and am trying to import a large text file (up to 30mb) that is defined by position i.e., space 1-8 = ID, space 9-14 = Description, etc. The only option for import is via SQL or CSV file.
Text File Import
I'm importing a large text file (8 million lines) into a mysql table via the heidisql front end. Each line has 42 fields (tab separated) so there's a lot of data to bring in. I tested the import with a truncated version of the text file (10,000 lines) and the import worked fine. It took about a minute (i think) to import. Now though, the full version (which is 800x as long) has been importing for nearly 48 hours. Heidisql seems to be still working - it hasn't frozen, it's busy. Is it normal for a file of this size to take so long to import?
Import A Text File
I have a utf8 txt file to import in a MySQL db, cause I must create a web-application in PHP for reading this information on-line. I have create a new DB in MYSQL 4.1.1a setting CHARACTER=utf8, then I have create a table t1 with character set utf8 and some fileds also with CHARACTER=utf8. Then I imported the Txt file (really in UTF8 format, with 22600 records!) in my db. MySQL have imported the non-utf8 field correctly, 26000 records. When I try to read this utf8 filed they appear like "???-?-???". Then, also my PHP application show this field with stranges characters. I have try also with PHPfunction utf8_encode with no success
Import Text And Csv File
i want to import either text or csv file to database how can i do this..i have tried mysqlimport command but its giving some error
MySQL Import Text File Too Big?
Our production database in an exported textfil runs about 60 MB. Compressed that's about 9 MB. I'm trying to import the export into another machine running FC3 and mySQL 11.18, and it appears as though the file may be too big! When I try to do it via command line: mysql -u root --host=localhost printing < ./printing.txt It eventually errors out with a "syntax error on line X" and only about half the tables get imported, and MyPHPAdmin GUI interface says it has a 2MB file limit on importing. This concerns me. If something were to happen to our production database, how am I going to restore it? I tried to do a search on mysql.com, but that's a less than helpful site for support. A newsgroup search on the topic didn't come up with much, so maybe I'm just bad at using keywords.
Import A Text Backup Of SQL Db Into Another One On Linux
I have a SQL file, which is a backup of a database (data+scheme). The file was generated using MySQLGUI backup features on a windows 2k3 server. Now I would like to import this data + scheme into a mysql server running on linux, there is no GUI on the machine. 2 questions if I may: 1. How do I import the described above data. 2. Will the meta data (permissions, collation & co.) will be restored as well, or will I need to reconfigure them ?
Data Import From Text File
When I try to import a text file with new data for an existing table I get the error "1148 - the used command is not allowed with this MySQL version." I have tried with both PHPMyAdmin2.3 and with MySQLFront 2.5. Both these GUI programmes have an "import from text file" command which I have used successfully several times to add entries to this table before - running just this MySQL version. I have structured the data in the text file in just the same format as previously. Both these programmes are connecting to the database successfully now and will display the contents of the existing table data and strcutre of the database etc (despite an earlier connection problem which seems to have been solved spontaneously). But both now deliver exactly this error message (and therefore I assume it is MySQL and not the interface programmes which is doing it).I cannot see that I am doing anything different. I have MySQL 4.0.12 on Windows ME PC with a local Apache server and PHP.
Error In Mysql Import (text File)
Error SQL query: -- phpMyAdmin SQL Dump -- version 2.8.0.2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: May 27, 2006 at 12:21 AM -- Server version: 4.1.18 -- PHP Version: 4.4.2 -- -- Database: `lopata_trnew` -- -- -------------------------------------------------------- -- -- Table structure for table `Register` -- CREATE TABLE `Register` ( `id` int( 10 ) unsigned NOT NULL AUTO_INCREMENT , `Date` date default NULL , `Name` text, `Company` text, `Address` text, `City` text, `State` tinytext, `Zip` text, `marketarea` text, `Phone` text, `Email` text, `Comments` text, `OwnerNameA1` text, `OwnerNameB1` text, `PropAddr1` text, `PropZip1` text, `OwnerNameA2` text, `OwnerNameB2` text, `PropAddr2` text, `PropZip2` text, `OwnerNameA3` text, `OwnerNameB3` text, `PropAddr3` text, `PropZip3` text, PRIMARY KEY ( `id` ) ) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =49; MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' CREATE TABLE `Register` ( `id` int(10) unsigned NOT NULL auto_increm' at line 1
Problem With Data Import From Text File
When I try to import a text file with new data for an existing table I get the error "1148 - the used command is not allowed with this MySQL version." I have tried with both PHPMyAdmin2.3 and with MySQLFront 2.5. Both these GUI programmes have an "import from text file" command which I have used successfully several times to add entries to this table before - running just this MySQL version. I have structured the data in the text file in just the same format as previously. Both these programmes are connecting to the database successfully now and will display the contents of the existing table data and strcutre of the database etc (despite an earlier connection problem which seems to have been solved spontaneously). But both now deliver exactly this error message (and therefore I assume it is MySQL and not the interface programmes which is doing it). I cannot see that I am doing anything different. I have MySQL 4.0.12 on Windows ME PC with a local Apache server and PHP. PS I sent a long message about this and a connection problem yesterday which was not succint enough.
Import Structured Text File Into Mysql
I've a lot of structured files like the following one I would like to import in a mysql database: Message-ID: <417ee59f@mail-ha1> Subject: test From: renu [renu@solicomm.net] Newsgroups: Practice <html> <head> </head> <body> <p> xghdgdfgfgdff </p> <p> xgdsfssdfsdf fsdfsfsdfhdgdfgfgdff </p> </body> </html> I need to import the first 4 rows in 4 columns (messageid, subject, form, newsgroups) and the last part of the text, which is in html, in a 5th colum. The fact is that I'm not sure whether it's possible. Should it be possible, could someone point me to the method/tool that could help me in doing that?
Trying To Import A Text File That Has A Non-standard Encoding
I have written a applicaiton that reads in a text file and then parses each line to store a record in MySQL. I'm having problem with only this one file where when I open with notepad, there is no new line and instead the line breaker character is replaced with a number like 105788, 105704, 105708, and so on (its not the same number for each line) However, when I open this same file with wordpad, the new line shows correctly. I was wondering if anyone can point out some suggestion as to how I can effectively import this file. Currently, my application is dependent on the fact that each field is seperated by a new line.
Import Data From Text File Mysql
Im going to write a script in linux that updates my mysql database. How can i write a sql statemtn that updates my mysql table from a text file located on my machine? lil confused bout the path of the txt file?
Orderby A Text Field As It Were An Integer Field
Is it possible to construct a query so that the orderby 'field' returns the rows as though the 'field' were an integer field, even though it is a text field? I do not want e.g. 10 to be before 2. The field in question is called 'lagerantal' and is of type 'text'
Import A Directory Of Text Files Into A Mysql Database With Php?
I have a directory of text files that i'd like to import into a msql table... I was wondering if there's an easier way to do this with php/mysql, I'd like to avoid copy/pasting the entire directory (thousands of files) into the database if i can. The directory structure of the text files resembles... somepath/A/Aerosmith/Song Name.txt - (extention varies) What i'd like to do is use php to scan the directory and input the file path information and the content from each file into a table like... Dir Artist Song Text Type ------------------------------------------------------------------------ A Aerosmith Song Name Fa La La La La (example text) File Ext B Beatles Song Name Fa La La La La (example text) File Ext
Schedule Auto Import New Log / Text Files Into MySQL
Basically we have a SQL Server Database on our telecoms switch, to which under the maintenance contract we have no direct access. Every 15 mins log files are downloaded in *.log files (text files) which contain data we can upload to our own databases for querying. The log files are on an ftp server and the filenames comprise of date and time of dumps. Requirement : In short I would like to know the best way to automatically update tables on a MySQL database with the new log files as they appear or if there is some existing software which allows this type of schedule and auto import. I would be very greatful if someone can give me some ideas with this problem as it would save me so much time and I am most familiar with mySql and Sql Server databases and would appreciate help in either database.
Limiting The Lenght Of Result
let say i have a column news that have more than 1000 varchar, what should i do if i want to limit the display to only 100 char.
LOAD DATA INFILE Command To Import A Text File
I am using the LOAD DATA INFILE command to import a text file into mysql. However, there is much more text in the file than needed in my tables. How can I make a selection from the text line. For example position 5 - 10 in field A, position 20-23 in field B, etc ...
How To Track IPs With Time Lenght Of Access
I have a problem with my website, cose I have this visitor (most probably an abusive web crawler..LiveMessage Crawler?!)that has been connected almost 24/7 for more than 2 weeks now. Im tired of it. I banned the IP with the forum function....and I still cant understand why it doesnt work....even if Im gonna try to ban that Ip in some other way, I still need your help ) So, I sent an email to that ip ISP, and they asked me for all the logs, with that IP accesses, lenght of the access and so on.....Im not sure but I dont think there is already this function in MySQL (Client API version 3.23.49) Now....is it possible to do something with MySQL to make a new file which tracks..logs..every access from visitors IPs and the lenght of their stay in the website for every day?
Ascii Into MySQL
I'm trying to find a way of receiving an 'ascii file' and transferring this automatically into an MySQL database, in order to form the basis for a web reporting webpage.
Adding A Random Number To A Table Field During Import?
By habit, my ID fields are usually md5() generated - 32 character varchar field. When I import data using LOAD DATA INFILE, is there a way to generate that number for the have a table field since it is not in the infile? If not, is there another mechanism for importing that would allow for that? If not, are there operational security issues you guys are concerned with using auto-increment like being bale to guess another record number?
Import Additional Field Data To Existing Table
I've got my data all set-up in a mysql table and all functioning well. We have decided that we need some additional bits of data for each record and we have that data in a csv file. We've created the fields in the mysql table. In the csv file, I have the userid (to match that in the current sql table) and the additional data with the column names in the csv file matching exactly the new fields in the sql table. The additional fields in the original sql table are empty, so we can just simply write-over these fields with the data from the csv file. But...we are having problems with the import using the MySql administration tool on our server. If I select a csv file upload, I get a number of fields is not same error If I use csv load data upload, it just overwrites the first few lines of current data and doesn't put the new data in the correct fields. Can anybody help, please? If csv load data is the correct format to use, what do I put in the import tool for these variables (given that I have a standard csv file that has been created using excel) Replace table data with file (yes/no default is no) Ignore duplicate rows (yes/no - default is no) Fields terminated by(default is Fields enclosed by(default is ") Fields escaped by(default is ) Lines terminated by(default is auto) Column names (default is blank) Use LOCAL keyword (yes/no - default is yes)
Reading Latin1 Ascii Into Mysql
I have a list of ascii data that I want to import into mysql. The character however is in the German alphabet, so it contains some äöüß and so on, but a simple mysql import via a command mysql < file.sql does not seem to do the trick, as the characters has to be converted to multi bit values. My question is, is there a simple way to do this, or would I need to write a php script that will read the file in, and save it in the DB record for record. I reckon that this would solve the problem, but am not sure. So before I spend a couple of hours coding some program that might not work, I wish to ask someone here for some suggestions.
MYSQL MATCH AGAINST Search, ASCII Characters, Etc.
Well, I am trying to make an internal site search for sometime now, and now I'm having this problem. I have non english characters, like é, è, ç, à, etc. These characters are stored in the database in ascii format. Now let's take a name Ségolène, this is stored in the database as Ségolène Now to search that out, I need to make the word as "Ségolène" (double quoted). Problem is, if I double quote, I get exact non english words, but I can't get nearby words, like I can't get Ségolène just by typing Ségo. As, to get Ségolène by typing Ségo, I need to make ségo* and this * and " " are not matching together.
Filtering Odd Ascii Characters From MYSQL Input
My users are sometimes pasting "funny" quotation marks into their FCKEditor, also some kind of elongated hypen. How can I filter these out before writing the user input to mySQL? Obviously I could filer each one out, but I rather like something more generic.
Where To Put TEXT Field?
I have a table to which I want to add, optionally for each record, a rather long descriptive text, as a TEXT field. Only some fraction of the records will have this field filled, and the field will not be used in queries (besides to retrieve its value). I can either put this field right in the main table, or create a child table with a link back to the primary key of the main table. Is the second approach more efficient with database space than the first?
TEXT Field
I am trying to run an ALTER on a table. I am trying to do the following: ALTER TABLE products ADD txtErrorMsg10 TEXT NOT NULL default 'My Error Message.' .. But I get some error about the field isn't allowed to have a default value. Is what I'm trying to do possible?
Text Field Formatting
I am on OS X, Apache, using MYSQL and PHP developing a database for my community theatre that includes a table for Board meeting minutes with the minutes being stored in a Text Field. I don’t need a whole lot of fancy formatting, but I would like to include a blank line between paragraphs. This does not seem to be a problem when the text is displayed within a text field within a form. I can include any number of lines between paragraphs and MySQL stores them and redisplays within the form. The problem occurs when I bind the field to a page to display the minutes without a form field (and scroll bars) for printing. All the lines will run together with no breaks unless I use the php nl2br formatting command. Even then, only one <br > is inserted, regardless of how many blank lines are in the text field. Any suggestion on how I can maintain a blank line between paragraphs? I know...the best way is to store the text as html and then display it via echo, but this is being designed for people who do not know html and will be typing up the minutes on their home computer.
Search In A Text Field
I have a text field that holds a description. For example we will say the description is for a tree so it would be "A tall plant with a bushy green top." Is there anyway for when a user searches for "Tall Plant" that "tree" will be returned? I can not think of a select statement that would accomplish this, is there any kind of function such as isIn() or something?
SELECT TEXT FIELD
I want to do a SELECT query to display data from a TEXT field. It doesn't work with a simple SELECT statement and I've looked all over the books / documentation for the syntax but can't find anything. I keep getting a check syntax error. I want to display text data from a field called DESC in a Books table for a bookselling website as in : SELECT BOOK DESC FROM allbooks WHERE BOOK = Number ( BOOK is the ID number ) (I know that in an object based DB I have used there is a READ comand and a WRITE command to be used with text streams but how do you do it with SQL / MySQL ? )
Size Of Text Field
Each time I create a record with a Text Field, does it take up the entire 65535 bytes or just use what it needs?
Can't UPDATE A TEXT Field
I am creating an events calendar and one of the fields is a description field, which is a TEXT field. I am unable to use the UPDATE command to update this TEXT field. I have read that for MS SQL, you need to use UPDATETEXT but alas, I am using MySQL and there is no such command. I have found nothing in the MySQL documentation on how to update this TEXT field. Could someone shed some light on this, or direct me to the article in the documentation on how to update TEXT fields?
Serach In Text Field
I want to search for this string in an text field: ' b)' ok, will not look the same in this forum, but I've read out the ascii code for this and I got this 'CHAR(10)CHAR(32)CHAR(32)' and then this 'b)' Now the final result of this string is 'CHAR(10)CHAR(32)CHAR(32)b)' and tried it with this statement: SELECT blabla FROM myTable WHERE myTextField LIKE CONCAT('%', CHAR(10), CHAR(32), CHAR(32), 'b)%') but it doesn't work.
Adding To A Text Field
is there anyway to append to a text field without having to select it first, add the data and then update the record?
|
TRACKER
