Styjun

Are we sinners because we sin or do we sin because we are sinners?

Is there a heavy usage of the word "bonfire" in English?

Two button calculator part 2

Is it a mistake to use a password that has previously been used (by anyone ever)?

What is :>filename.txt Doing?

Isn't any conversation with the US president quid-pro-quo?

What other tricks were there to get more data onto floppy disks?

Beautiful planar geometry theorems not encountered in high school

Do any other countries aside from the UK have a tradition of ‘joke’ candidates?

Locked folder with obscure app from Sourceforge, now cannot unlock folder

How to break a equation with a single "summation symbol (sum) " common?

Bash script that shows changing real time values from commands

How do critical hits work with static monster damage?

Stack data structure in python 3

Elliptic curve commitments mod p

Days in indexed month

Why does rapeseed oil turn sticky but coconut oil doesn't?

How to write a vertically centered asterisk in LaTex in a normal text?

NP-hard problems but only for n≥3

N-Dimensional Cartesian Product

RPMs too high on freeway? Help!

Why are session states synchronized with high availability?

What latex template to use when you do not know the journal you are going to submit

Elevator design implementation in C++

SQL LOAD FILE fills table with zeros/null values

How to list the tables in a SQLite database file that was opened with ATTACH?Add a column with a default value to an existing table in SQL ServerSQL update from one Table to another based on a ID matchFinding duplicate values in a SQL tableFind all tables containing column with specified name - MS SQL ServerSQL select only rows with max value on a columnHow to drop a table if it exists?How do I import an SQL file using the command line in MySQL?PHP/MySQL: importing CSV, all date and decimal value are null, zeroRun mysql commands on wordpress

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;

0

So, I have an issue with loading my CSV into SQL database with PHP and Wordpress. Both are running on local xammp atm.

This is my code for loading the CSV file. It first uploads it via html form to some temp folder (data) and then tries with LOAD DATA to copy the content into the table (wp_data)

<?php
if (isset($_POST['submit'])) 
 global $wordpress,$wpdb;
 $file = $_FILES['fileToUpload']['tmp_name'];
 $target_dir = $_SERVER['DOCUMENT_ROOT']."/data";
 $target_file = $target_dir . '/' . basename($_FILES["fileToUpload"]["name"]);
 move_uploaded_file($file, $target_file);

 $sql="
 LOAD DATA INFILE '$target_file' INTO TABLE wp_data FIELDS TERMINATED BY ';' IGNORE 1 ROWS";
 $query = $wpdb->query($sql);

?>

The uploading part works fine, however when it tries to fill the table with the data, it just fills "0s"/NULL values, like this:

The file as text looks like this:

And visually, it looks like this:

Table structure from SHOW CREATE TABLE wp_data:

CREATE TABLE `wp_data` (
 `Unix_time` int(11) NOT NULL,
 `Message` text NOT NULL,
 `Time` float NOT NULL,
 `DF` int(11) NOT NULL,
 `Type_code/BDS_number` int(11) NOT NULL,
 `Latitude` double NOT NULL,
 `Longitude` double NOT NULL,
 `Altitude` int(11) NOT NULL,
 `Ground_speed` double NOT NULL,
 `Baro_diff` int(11) NOT NULL,
 `Heading` int(11) NOT NULL,
 `TAS` int(11) NOT NULL,
 `Mach` float NOT NULL,
 `FOM_SOURCE` varchar(40) DEFAULT NULL,
 `Wind_speed` varchar(40) DEFAULT NULL,
 `Wind_direction` varchar(40) DEFAULT NULL,
 `Temperture` text,
 `Pressure` varchar(40) DEFAULT NULL,
 `Turbulence` varchar(40) DEFAULT NULL,
 `Humidity` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

What am I doing wrong here? I am quite new at PHP and got stuck on this
:(...

Thx.

php sql wordpress mariadb

share|improve this question

edited Mar 28 at 22:57

Václav

asked Mar 28 at 22:10

VáclavVáclav

54922 silver badges1919 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  show the table structure? SHOW CREATE TABLE wp_data edit the question with the information?
  
  – Raymond Nijland
  Mar 28 at 22:14
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  And write the file encoding here as well... pretty much looks like you have an UCS-2 encoded file and all characters are 2-byte and didn't handle it properly..
  
  – Lars Stegelitz
  Mar 28 at 22:17
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  PHP has a function mb_detect_encoding() which you can use if you read the uploaded file with file_get_contents()
  
  – Raymond Nijland
  Mar 28 at 22:20
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  notepad++ or any decent editor should be able to tell you the encoding.. otherwise, ask the source of the file (how was this file created? by whom?) :)
  
  – Lars Stegelitz
  Mar 28 at 22:20
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  also to add to @LarsStegelitz comment.. PHP MySQL's Clients defaults into using latin1 charset.. You might need to set that also to the correct encoding with mysqli_set_charset() for example
  
  – Raymond Nijland
  Mar 28 at 22:23
  

  
  
  
  

 | 
show 7 more comments

0

So, I have an issue with loading my CSV into SQL database with PHP and Wordpress. Both are running on local xammp atm.

This is my code for loading the CSV file. It first uploads it via html form to some temp folder (data) and then tries with LOAD DATA to copy the content into the table (wp_data)

<?php
if (isset($_POST['submit'])) 
 global $wordpress,$wpdb;
 $file = $_FILES['fileToUpload']['tmp_name'];
 $target_dir = $_SERVER['DOCUMENT_ROOT']."/data";
 $target_file = $target_dir . '/' . basename($_FILES["fileToUpload"]["name"]);
 move_uploaded_file($file, $target_file);

 $sql="
 LOAD DATA INFILE '$target_file' INTO TABLE wp_data FIELDS TERMINATED BY ';' IGNORE 1 ROWS";
 $query = $wpdb->query($sql);

?>

The uploading part works fine, however when it tries to fill the table with the data, it just fills "0s"/NULL values, like this:

The file as text looks like this:

And visually, it looks like this:

Table structure from SHOW CREATE TABLE wp_data:

CREATE TABLE `wp_data` (
 `Unix_time` int(11) NOT NULL,
 `Message` text NOT NULL,
 `Time` float NOT NULL,
 `DF` int(11) NOT NULL,
 `Type_code/BDS_number` int(11) NOT NULL,
 `Latitude` double NOT NULL,
 `Longitude` double NOT NULL,
 `Altitude` int(11) NOT NULL,
 `Ground_speed` double NOT NULL,
 `Baro_diff` int(11) NOT NULL,
 `Heading` int(11) NOT NULL,
 `TAS` int(11) NOT NULL,
 `Mach` float NOT NULL,
 `FOM_SOURCE` varchar(40) DEFAULT NULL,
 `Wind_speed` varchar(40) DEFAULT NULL,
 `Wind_direction` varchar(40) DEFAULT NULL,
 `Temperture` text,
 `Pressure` varchar(40) DEFAULT NULL,
 `Turbulence` varchar(40) DEFAULT NULL,
 `Humidity` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

What am I doing wrong here? I am quite new at PHP and got stuck on this
:(...

Thx.

php sql wordpress mariadb

share|improve this question

edited Mar 28 at 22:57

Václav

asked Mar 28 at 22:10

VáclavVáclav

54922 silver badges1919 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  show the table structure? SHOW CREATE TABLE wp_data edit the question with the information?
  
  – Raymond Nijland
  Mar 28 at 22:14
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  And write the file encoding here as well... pretty much looks like you have an UCS-2 encoded file and all characters are 2-byte and didn't handle it properly..
  
  – Lars Stegelitz
  Mar 28 at 22:17
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  PHP has a function mb_detect_encoding() which you can use if you read the uploaded file with file_get_contents()
  
  – Raymond Nijland
  Mar 28 at 22:20
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  notepad++ or any decent editor should be able to tell you the encoding.. otherwise, ask the source of the file (how was this file created? by whom?) :)
  
  – Lars Stegelitz
  Mar 28 at 22:20
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  also to add to @LarsStegelitz comment.. PHP MySQL's Clients defaults into using latin1 charset.. You might need to set that also to the correct encoding with mysqli_set_charset() for example
  
  – Raymond Nijland
  Mar 28 at 22:23
  

  
  
  
  

 | 
show 7 more comments

So, I have an issue with loading my CSV into SQL database with PHP and Wordpress. Both are running on local xammp atm.

This is my code for loading the CSV file. It first uploads it via html form to some temp folder (data) and then tries with LOAD DATA to copy the content into the table (wp_data)

<?php
if (isset($_POST['submit'])) 
 global $wordpress,$wpdb;
 $file = $_FILES['fileToUpload']['tmp_name'];
 $target_dir = $_SERVER['DOCUMENT_ROOT']."/data";
 $target_file = $target_dir . '/' . basename($_FILES["fileToUpload"]["name"]);
 move_uploaded_file($file, $target_file);

 $sql="
 LOAD DATA INFILE '$target_file' INTO TABLE wp_data FIELDS TERMINATED BY ';' IGNORE 1 ROWS";
 $query = $wpdb->query($sql);

?>

The uploading part works fine, however when it tries to fill the table with the data, it just fills "0s"/NULL values, like this:

The file as text looks like this:

And visually, it looks like this:

Table structure from SHOW CREATE TABLE wp_data:

CREATE TABLE `wp_data` (
 `Unix_time` int(11) NOT NULL,
 `Message` text NOT NULL,
 `Time` float NOT NULL,
 `DF` int(11) NOT NULL,
 `Type_code/BDS_number` int(11) NOT NULL,
 `Latitude` double NOT NULL,
 `Longitude` double NOT NULL,
 `Altitude` int(11) NOT NULL,
 `Ground_speed` double NOT NULL,
 `Baro_diff` int(11) NOT NULL,
 `Heading` int(11) NOT NULL,
 `TAS` int(11) NOT NULL,
 `Mach` float NOT NULL,
 `FOM_SOURCE` varchar(40) DEFAULT NULL,
 `Wind_speed` varchar(40) DEFAULT NULL,
 `Wind_direction` varchar(40) DEFAULT NULL,
 `Temperture` text,
 `Pressure` varchar(40) DEFAULT NULL,
 `Turbulence` varchar(40) DEFAULT NULL,
 `Humidity` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

What am I doing wrong here? I am quite new at PHP and got stuck on this
:(...

Thx.

php sql wordpress mariadb

share|improve this question

edited Mar 28 at 22:57

Václav

asked Mar 28 at 22:10

VáclavVáclav

54922 silver badges1919 bronze badges

<?php
if (isset($_POST['submit'])) 
 global $wordpress,$wpdb;
 $file = $_FILES['fileToUpload']['tmp_name'];
 $target_dir = $_SERVER['DOCUMENT_ROOT']."/data";
 $target_file = $target_dir . '/' . basename($_FILES["fileToUpload"]["name"]);
 move_uploaded_file($file, $target_file);

 $sql="
 LOAD DATA INFILE '$target_file' INTO TABLE wp_data FIELDS TERMINATED BY ';' IGNORE 1 ROWS";
 $query = $wpdb->query($sql);

?>

CREATE TABLE `wp_data` (
 `Unix_time` int(11) NOT NULL,
 `Message` text NOT NULL,
 `Time` float NOT NULL,
 `DF` int(11) NOT NULL,
 `Type_code/BDS_number` int(11) NOT NULL,
 `Latitude` double NOT NULL,
 `Longitude` double NOT NULL,
 `Altitude` int(11) NOT NULL,
 `Ground_speed` double NOT NULL,
 `Baro_diff` int(11) NOT NULL,
 `Heading` int(11) NOT NULL,
 `TAS` int(11) NOT NULL,
 `Mach` float NOT NULL,
 `FOM_SOURCE` varchar(40) DEFAULT NULL,
 `Wind_speed` varchar(40) DEFAULT NULL,
 `Wind_direction` varchar(40) DEFAULT NULL,
 `Temperture` text,
 `Pressure` varchar(40) DEFAULT NULL,
 `Turbulence` varchar(40) DEFAULT NULL,
 `Humidity` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

share|improve this question

edited Mar 28 at 22:57

Václav

asked Mar 28 at 22:10

VáclavVáclav

54922 silver badges1919 bronze badges

share|improve this question

edited Mar 28 at 22:57

Václav

asked Mar 28 at 22:10

VáclavVáclav

54922 silver badges1919 bronze badges

asked Mar 28 at 22:10

VáclavVáclav

54922 silver badges1919 bronze badges

asked Mar 28 at 22:10

VáclavVáclav

54922 silver badges1919 bronze badges

1 Answer
1

active

oldest

votes

1

Fixed it by setting the encoding in query to CHARACTER SET UTF8 like this:

LOAD DATA INFILE '$target_file'
INTO TABLE wp_data 
CHARACTER SET UTF8 
FIELDS TERMINATED BY ';'
IGNORE 1 ROWS;

share|improve this answer

edited Mar 28 at 23:03

GMB

41.3k77 gold badges1414 silver badges3535 bronze badges

answered Mar 28 at 22:56

VáclavVáclav

54922 silver badges1919 bronze badges

add a comment
 | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55407628%2fsql-load-file-fills-table-with-zeros-null-values%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Fixed it by setting the encoding in query to CHARACTER SET UTF8 like this:

LOAD DATA INFILE '$target_file'
INTO TABLE wp_data 
CHARACTER SET UTF8 
FIELDS TERMINATED BY ';'
IGNORE 1 ROWS;

share|improve this answer

edited Mar 28 at 23:03

GMB

41.3k77 gold badges1414 silver badges3535 bronze badges

answered Mar 28 at 22:56

VáclavVáclav

54922 silver badges1919 bronze badges

add a comment
 | 

1

Fixed it by setting the encoding in query to CHARACTER SET UTF8 like this:

LOAD DATA INFILE '$target_file'
INTO TABLE wp_data 
CHARACTER SET UTF8 
FIELDS TERMINATED BY ';'
IGNORE 1 ROWS;

share|improve this answer

edited Mar 28 at 23:03

GMB

41.3k77 gold badges1414 silver badges3535 bronze badges

answered Mar 28 at 22:56

VáclavVáclav

54922 silver badges1919 bronze badges

add a comment
 | 

Fixed it by setting the encoding in query to CHARACTER SET UTF8 like this:

LOAD DATA INFILE '$target_file'
INTO TABLE wp_data 
CHARACTER SET UTF8 
FIELDS TERMINATED BY ';'
IGNORE 1 ROWS;

share|improve this answer

edited Mar 28 at 23:03

GMB

41.3k77 gold badges1414 silver badges3535 bronze badges

answered Mar 28 at 22:56

VáclavVáclav

54922 silver badges1919 bronze badges

LOAD DATA INFILE '$target_file'
INTO TABLE wp_data 
CHARACTER SET UTF8 
FIELDS TERMINATED BY ';'
IGNORE 1 ROWS;

share|improve this answer

edited Mar 28 at 23:03

GMB

41.3k77 gold badges1414 silver badges3535 bronze badges

answered Mar 28 at 22:56

VáclavVáclav

54922 silver badges1919 bronze badges

edited Mar 28 at 23:03

GMB

41.3k77 gold badges1414 silver badges3535 bronze badges

edited Mar 28 at 23:03

GMB

41.3k77 gold badges1414 silver badges3535 bronze badges

answered Mar 28 at 22:56

VáclavVáclav

54922 silver badges1919 bronze badges

answered Mar 28 at 22:56

VáclavVáclav

54922 silver badges1919 bronze badges