columns ⋆ 100% Private Proxies - Fast, Anonymous, Quality, Unlimited USA Private Proxy!

load local data on fixed width file inserting all columns with NULL values

Last month I had occasion to load about 50 GB of data from fixed-width dat files. There were 7 files so I created 7 tables. I created 7 LOAD DATA LOCAL INFILE scripts to load the data and it all worked fine resulting in over 70 million rows. The data is available with updates on the 10th of the month so I downloaded the files and ran my scripts on them and only 1 worked and the rest loaded the number of rows but with all of the columns NULL. I’ve compared the previous files with the new ones and cannot find any difference that would cause this. I’ve put a small amount of data that’s failing in a test.dat file and am getting the same results, but have not been able to determine why this is happening or why one works and the rest don’t? I don’t see any difference between the files or the sql that loads them? I’ve tried changing the encoding, line endings, permissions, ownership, and various other things without success. There are no errors thrown, it just loads NULL values. Has anyone ran across this before?

Here is an example table with the load sql

DROP TABLE IF EXISTS exp_gpoper; CREATE TABLE `exp_gpoper` (   `_id` int(11) NOT NULL AUTO_INCREMENT,   `date_updated` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),   `pun_county_num` varchar(100) DEFAULT '',   `pun_lease_num` varchar(100) DEFAULT '',   `pun_sub_num` varchar(100) DEFAULT '',   `pun_merge_num` varchar(100) DEFAULT '',   `company_number` varchar(100) DEFAULT '',   `company_name` varchar(300) DEFAULT '',   PRIMARY KEY (`_id`),   KEY `date_updated` (`date_updated`),   KEY `pun_county_num` (`pun_county_num`),   KEY `pun_lease_num` (`pun_lease_num`),   KEY `pun_merge_num` (`pun_merge_num`),   KEY `pun_sub_num` (`pun_sub_num`),   KEY `company_number` (`company_number`),   KEY `company_name` (`company_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  LOAD DATA LOCAL INFILE 'test.dat'  INTO TABLE exp_gpoper  (@_row)  SET `pun_county_num` = TRIM(SUBSTR(@row,1,3)),  `pun_lease_num` = TRIM(SUBSTR(@row,4,6)),  `pun_sub_num` = TRIM(SUBSTR(@row,10,1)),  `pun_merge_num` = TRIM(SUBSTR(@row,11,4)),  `company_number` = TRIM(SUBSTR(@row,15,7)),  `company_name` = TRIM(SUBSTR(@row,22,255));

Here is the content of the test.dat file:

001000000000000077777OTC USE                                                                                                                                                                                                                                                         003000000000000077777OTC USE                                                                                                                                                                                                                                                         003000567000000020011M & D PUMPING SERVICE INC                                                                                                                                                                                                                                       003000587000000022576SCOGGINS PRODUCTION LLC                                                                                                                                                                                                                                         003000588000000022576SCOGGINS PRODUCTION LLC                                                                                                                                                                                                                                         003000639000000017441CHESAPEAKE OPERATING LLC                                                                                                                                                                                                                                        003000963000000019694BVD INC                                                                                                                                                                                                                                                         003000964000000018119BLAKE PRODUCTION CO INC                                                                                                                                                                                                                                         003002207124830022281SANDRIDGE EXPLORATION AND PRODUCTION LLC                                                                                                                                                                                                                        003002394000000020891SUPERIOR OIL & GAS LLC

This works fine:

DROP TABLE IF EXISTS `exp_gpexempt`; CREATE TABLE `exp_gpexempt` (   `_id` int(11) NOT NULL AUTO_INCREMENT,   `date_updated` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),   `pun_county_num` varchar(100) DEFAULT '',   `pun_lease_num` varchar(100) DEFAULT '',   `pun_sub_num` varchar(100) DEFAULT '',   `pun_merge_num` varchar(100) DEFAULT '',   `exemption_type` varchar(100) DEFAULT '',   `code` varchar(100) DEFAULT '',   `exemption_percentage` varchar(100) DEFAULT '',   PRIMARY KEY (`_id`),   KEY `date_updated` (`date_updated`),   KEY `pun_county_num` (`pun_county_num`),   KEY `pun_lease_num` (`pun_lease_num`),   KEY `pun_merge_num` (`pun_merge_num`),   KEY `exemption_type` (`exemption_type`),   KEY `code` (`code`),   KEY `exemption_percentage` (`exemption_percentage`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  LOAD DATA LOCAL INFILE 'test2.dat'  INTO TABLE exp_gpexempt (@_row) SET `pun_county_num` = TRIM(SUBSTR(@_row,1,3)), `pun_lease_num` = TRIM(SUBSTR(@_row,4,6)), `pun_sub_num` = TRIM(SUBSTR(@_row,10,1)), `pun_merge_num` = TRIM(SUBSTR(@_row,11,4)), `exemption_type` = TRIM(SUBSTR(@_row,15,50)), `code` = TRIM(SUBSTR(@_row,65,5)), `exemption_percentage` = TRIM(SUBSTR(@_row,70,24));

Here is the content of the test2.dat file:

00300063900000School District                                   05   00000000000.000293000000 00300365500000State School Land Commission                      01   00000000000.125000000000 00301843300000State School Land Commission                      01   00000000000.125000000000 00302942700633State School Land Commission                      01   00000000000.125000000000 00302942800633State School Land Commission                      01   00000000000.125000000000 00303004100000Federal                                           02   00000000000.067632900000 00303004200000Federal                                           02   00000000000.125000000000 00303004600000Federal                                           02   00000000000.125000000000 00303004700000Federal                                           02   00000000000.125000000000 00303004800000Federal                                           02   00000000000.125000000000

Upsert Row If Columns Don’t Match

I have a table with unique identifier sid, somecolumn and boolean changed that is needed by other program to detect change in row, I’m using insert into ... on conflict (sid) DO UPDATE ... to insert data that’s not already in there, but I also want to update somecolumn and set changed to true, only if somecolumn doesn’t match excluded one.

to make it bit clearer

if sid doesn’t exist, insert data
if sid exists and somecolumn matches excluded `somecolumn, do nothing
if sid exists and somecolumn doesn’t match, update somecolumn with new value and set changed to true

is there clean way to do this? I’d prefer to do this without making multiple queries, I’m inserting hundreds of values in each query.

In my layout 2 columns on small devices

Hello,
Could you please look at design of
http://vtasks.my-demo-apps.tk/
site

In my vue/cli 4 / Bootstrap 4.3 app I make listing of tasks in 1 column on small devices(ipad-s)
and 2 columns on big devices, like :

<div class="row " v-show="tasks.length && is_page_loaded"> <div v-for="(nextActiveTask, index) in tasks" :key="nextActiveTask.id" class="col-sm-12 col-md-6 p-2 m-0"> <task-list-item :currentLoggedUser="currentLoggedUser" :nextActiveTask="nextActiveTask" :index="index"...

HTML:

In my layout 2 columns on small devices

do columns and gutters in an 8pt grid system have to be multiples of 8?

for vertical spacing it makes sense to use 8px increments. However horizontally it seems impossible to maintain that specific spacing and have it be responsive.

Table Columns with Complex Structures (fields under it, that can have fields underneath too-like nested ones)in a few of its col

This is for Web Application. I am looking for tabular UIs that doesn’t have nesting but is able to show a child row under a parent row and those children can have further children and so on. My limitation is not to use nesting. Any leads or ideas around it? enter image description here

Does the order of columns in a covering index matter?

I have the following covering indices:

INDEX (col1, col3); -- index 1 INDEX (col1, col2, col3); -- index 2

because I would like to support the following type of queries:

  SELECT ...     FROM my_table    WHERE col1 = ... AND          col2 = ... ORDER BY col3

  SELECT ...     FROM my_table    WHERE col1 = ...  ORDER BY col3

I am unfamiliar with how a covering index works. Is index 1 redundant? Or does a covering index requires that the columns be side by side?

How can i write script for search two different List columns of search boxes related data show on grid view in SPFX?

Am able to search the PR_Number column data, the same i want to search for Description as input in another search box and show the gridview in SPFx.

import * as React from 'react'; import * as ReactDom from 'react-dom'; import { Version } from '@microsoft/sp-core-library'; import {   BaseClientSideWebPart,   IPropertyPaneConfiguration,   PropertyPaneTextField } from '@microsoft/sp-webpart-base'; //import styles from './PrFilter.module.scss'; import { escape } from '@microsoft/sp-lodash-subset';  import * as strings from 'PrFilterWebPartStrings'; import PrFilter from './components/PrFilter'; import { IPrFilterProps } from './components/IPrFilterProps'; import { SPHttpClient, SPHttpClientResponse } from '@microsoft/sp-http';  export interface IPrFilterWebPartProps {   description: string; } export interface ISPLists {   value: ISPList[]; } export interface ISPList {   PR_Number: string;   Description: string;   Request_Date: string; }      export default class PrFilterWebPart extends BaseClientSideWebPart<IPrFilterWebPartProps> {    private _getListData(): Promise<ISPLists> {       let queryString: string = '';     let queryStringforPR: string = '';     let searchboxVal: string=(this.domElement.querySelector('#searchbox') as  HTMLInputElement).value;     if(searchboxVal!=""){       // alert("searchboxVal" + searchboxVal);             queryString="$  filter=substringof('"+searchboxVal+"',PR_Number)";       queryStringforPR= searchboxVal;       console.log("qurery string value is " + queryString);       console.log("queryStringforPR value is " + queryStringforPR);     }     return this.context.spHttpClient.get(this.context.pageContext.web.absoluteUrl + `/_api/web/lists/GetByTitle('PurchaseRequest')/Items?$  filter=PR_Number eq $  {queryStringforPR}`, SPHttpClient.configurations.v1)         .then((response: SPHttpClientResponse) => {             return response.json();         });   }   private _renderListAsync(): void    {       this._getListData()     .then((response) => {       this._renderList(response.value);     });   }    private _renderList(items: ISPList[]): void    {     //debugger;     let html: string = '<table class="TFtable" border=1 width=100% style="border-collapse: collapse;">';     html += `<th>PR_Number</th><th>Description</th><th>Request_Date</th>`;     items.forEach((item: ISPList) => {       html += `           <tr>           <td>$  {item.PR_Number}</td>           <td>$  {item.Description}</td>           <td>$  {item.Request_Date}</td>           </tr>           `;     });     html += `</table>`;     const listContainer: Element = this.domElement.querySelector('#spListContainer');     listContainer.innerHTML = html;   }   private _setSearchBtnEventHandlers(): void    {     //debugger;     this.domElement.querySelector('#searchBtn').addEventListener('click', () => {         this._renderListAsync();     });   }     public render(): void {      this.domElement.innerHTML = `     <div>     <div>        <div class="ms-Grid-row ms-fontColor-white">         <div class="ms-Grid-col ms-u-lg10 ms-u-xl8 ms-u-xlPush2 ms-u-lgPush1">                  <p class="ms-font-l ms-fontColor-white" style="text-align: center">P2P Tool</p>         </div>       </div>       <div class="ms-Grid-row ms-fontColor-white ">          <input id="searchbox" type="textbox"/><input id="searchBtn" type="button" value="Search"/>         <input id="searchboxDescription" type="textbox"/><input id="searchBtnDescription" type="button" value="Search"/>              <br>         <div id="spListContainer" />       </div>     </div>   </div>`;     this._renderListAsync();     this._setSearchBtnEventHandlers();   }    protected get dataVersion(): Version {     return Version.parse('1.0');   }    protected getPropertyPaneConfiguration(): IPropertyPaneConfiguration {     return {       pages: [         {           header: {             description: strings.PropertyPaneDescription           },           groups: [             {               groupName: strings.BasicGroupName,               groupFields: [                 PropertyPaneTextField('description', {                   label: strings.DescriptionFieldLabel                 })               ]             }           ]         }       ]     };   } }

Having trouble with SPServices AutoComplete pulling from specific columns

I have 2 issues really. I have set this up successfully for some of my fields on my Nintex form. The autocomplete works perfectly.

Issue 1: I attempted to set this up on another field on another list. The field will not find anything when you start typing. I thought possibly I had the wrong crawled property, but I tried everything I could think of with no success.

Issue 2: I attempted to use a different column from the second list from Issue 1 to see if it was a column issue. I did get results, but they were not correct. In fact, I am unable to find ANYWHERE that the information in the auto-correct is displaying.

I can get the field to work if I connect it to the same columns as the other fields that are working. So it doesn’t appear to be a field or code issue. It seems that it either doesn’t like the list or the specific column.

I also tried to create a completely new list with column names I know are not being used anywhere else in our farm, but this didn’t work either.

I have no idea what’s going on. It’s quite puzzling. Below is my code.

NWF$  (document).ready(function() { //The source data for the autocomplete is the Journals list   //Store the journal titles in a array variable   var externalParties = []; NWF$  ().SPServices({     operation: "GetListItems",     async: false,     listName: "Vendor IDs",     CAMLViewFields: "<ViewFields><FieldRef Name='Vendor Name' /></ViewFields>", //the name needs to be changed accordingly        completefunc: function(xData, Status) {         NWF$  (xData.responseXML).SPFilterNode("z:row").each(function() {             externalParties.push(NWF$  (this).attr("ows_VendorName").substring(8));         });     } }); //journalinput is added on the control in the NF   NWF$  ("#" + journalinput4).autocomplete({     source: externalParties }); });